problem about postgresql-9.2-Collection of common programming errors

• 
  andreas-h
  postgresql permissions postgresql-9.2
  I’m wondering why a newly created user is allowed to create a table after connecting to a database. I have one database, project2_core:postgres=# \lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges —————+————–+———–+————-+————-+——————————-postgres | postgres | SQL_ASCII | C | C | project2_core | atm_project2 | UTF8 | de_DE.UTF-8 | de_DE.U
• 
  Erwin Brandstetter
  postgresql windows installation postgresql-9.2
  I am trying to get Postgres 9.2.4 to run as a service on Windows 7. After installing postgres, the service was running fine. However, after setting postgres up as a server for another program, the service stopped running. When I try to start the service now, I get a message saying :”The postgresql-x64-9.2 – PostgreSQL Server 9.2 service on LocalComputer started and then stopped. Some services stop automatically ifthey are not in use by other services or programs.”When I try running the program t
• 
  Stephane Rolland
  postgresql postgresql-9.2
  I’ve met this with 4 roles I created: After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more. pgAdmin III show error message:An error has occurred:Error connecting to the server: FATAL: password authentication failed for user “sam” FATAL: password authentication failed for user “sam”My system: Postgresql 9.2 on Ubuntu 12.04 Is there any way to fix this?(1): login with account postgres, right click user in Login Roles, go to tab ‘Definition’ and
• 
  user2291144
  jndi jboss5.x postgresql-9.2
  can someone figure out whats the error in jboss. when i enter localhost:8080 in url it says INVALID REQUEST. PLEASE CHECK URL.I am using jboss 5.0.1 JBOSS_HOME: D:\Jboss\jboss-5.0.1.GAJAVA: C:\Program Files\Java\jdk1.6.0_13\bin\javaJAVA_OPTS: -Dfile.encoding=UTF-8 -Dprogram.name=run.bat -server -Xms512m -Xmx1024m -XX:MaxPermSize=256m -Dorg.jboss.resolver.warning=true -Dsun.rmi.dgc.client.gcInterval=3600000 -Dsun.rmi.dgc.server.gcInterval=3600000CLASSPATH: D:\Jbos
• 
  Erwin Brandstetter
  sql arrays postgresql postgresql-9.2 window-functions
  I’m attempting to query a table which contains a character varying[] column of years, and return those years as a string of comma-delimited year ranges. The year ranges would be determined by sequential years present within the array, and years/year ranges which are not sequential should be separated be commas.The reason the data-type is character varying[] rather than integer[] is because a few of the values contain ALL instead of a list of years. We can omit these results.So far I’ve had littl
• 
  IanC
  arrays postgresql operators postgresql-9.2 hstore
  I can’t figure out how to re-write this query using arrays for the test cases:–explain SELECT COUNT(id) FROM (SELECT T.idFROM product2 AS TWHERE (ext @> ‘p01=>1’ OR ext @> ‘p01=>2’)AND (ext @> ‘p02=>1’ OR ext @> ‘p02=>2’ OR ext @> ‘p02=>3’)AND (ext @> ‘p03=>2’ OR ext @> ‘p03=>3’ OR ext @> ‘p03=>4’ OR ext @> ‘p03=>5’ OR ext @> ‘p03=>6’) ) TI’m looking for something like ext @> ‘p01=[1,2]’. The documentation doesn’t indicate
• 
  Erwin Brandstetter
  postgresql syntax types plpgsql postgresql-9.2
  I have created a custom typeCREATE TYPE rc_test_type AS (a1 bigint);and a function CREATE OR REPLACE FUNCTION public.rc_test_type_function(test_table character varying, dummy integer)RETURNS rc_test_type AS $BODY$ DECLAREret rc_test_type;query text;BEGINquery := ‘SELECT count(*) from ‘ || test_table ;EXECUTE query into ret.a1; RETURN ret; END $BODY$LANGUAGE plpgsql VOLATILEIf I runSELECT * FROM rc_test_type_function(‘some_table’, 1);I get “a1” 1389So far so good.If I runSELECT p FROM (SELE
• 
  Spaceman
  postgresql installation postgresql-9.1 postgresql-9.2
  So this morning I couldn’t install Postgres 9.1 from the Ubuntu repo. I tried installing 9.2 from postgres repo, but if failed with the same error. The error trace is really uninformative (I don’t even know what is the source of this error). Google didn’t tell me anything as well.It failed during installation with the same error, and I tried to create the cluster manually. But…root@Ubuntu-1304-raring-64-minimal /home/tmp # pg_createcluster 9.2 main –start Creating new cluster (configuration:
• 
  user234918
  recovery postgresql-9.2 database-backup
  I’m trying to set up backup strategy for a production postgreSQL DB. It will have large amount of data and it should be running 24 X 7. Could you recommend some backup & recovery strategies that can meet the following criteria?Large amount data (about over 200GB). Hot backup (online backup) Minimum Impact on the DB performance. Minimum restore time. Allow PITR(Point-In-Time Recovery)Can we execute backup with above criteria on slave server of replication? If you know backup strategy using st
• 
  yegor256
  postgresql postgresql-9.2
  My table is (there are some other columns):id INTEGER amount INTEGERThere is an index on amount. The query is:explain analyze select count(amount) from receiptOutput is:Aggregate (cost=215856.23..215856.23 rows=1 width=4) (actual time=180209.785..180209.787 rows=1 loops=1)-> Index Only Scan using idx_amount on receipt (cost=0.00..215046.23 rows=1620001 width=4) (actual time=0.109..177443.189 rows=2584317 loops=1)Heap Fetches: 2316761 Total runtime: 180209.868 msWhat’s going on? Index-only
• 
  marek
  mysql postgresql postgresql-9.2
  I have PostgreSQL 9.2 and MySQL 5.5 (InnoDB) installed on my laptop. Both database engines using default installation and populated from the same CSV file. I have ‘sales_reports’ table with ca. 700K rows.Scenario 1:following query:select name, year, region, branch from sales_reports group by name,year, region, branch;PostgreSQL 9.2: Total query runtime: 42.14 sec, 18064 rows retrieved PostgreSQL explain: Group (cost=165091.16..174275.61 rows=73476 width=58) (actual time=35196.959..41896.739 row
• 
  Borys
  indexing runtime postgresql-9.2 explain-plan
  I wonder why total runtime of count(*) is smaller than count(primary_key) of the same table? Qeury plan (using explain analyze) shows that every time Postgresql is doing Seq Scan instead of using indexes? Why is that happening? and why indexes aren’t used in this case?edited: count(1) doesn’t make any difference.
• 
  DiscontentDisciple
  performance postgresql postgresql-9.2
  I have finally gotten a Purpose Built Database machine for a project i’m working on. The server is a 4 Core Xeon with 64GB of Ram and a Raid10 of 10krpm drives. I have just got the database moved over to the new machine; the performance with the same code are worse than when it was running on a VM. I’m looking for suggestions on what settings to adjust to what values. Currently, I’ve upped shared_buffers to 60GB and the kernel settings needed to make that change.temp_buffers is 32MBwork_mem is 5
• 
  cimmanon
  postgresql optimization postgresql-9.2
  I’m working on a query in the same style as Facebook’s feed on a table that currently has 500,000 rows. For now I am just looking to get the 20 newest items (new records created by a user’s followees, records a user’s followees have shown interest in) sorted by whichever action was the most recent (add, poke, etc.) with no duplicates from the main table. Eventually there with be other types of activity that will influence the results, some of which must a CTE. Being able to paginate the resul
• 
  mcasfrox
  query-performance full-text-search postgresql-9.2
  Postgresql version 9.2.3!I’m working on a database for mapping of chemical names. My main table contains aprox 91 million records and it’s indexed by gin. I want to query it with multiple names (I’m trying now with 100 entries), which I first put in a query table, create a tsquery column of the names and index it with gist. Main table structure:CREATE TABLE IF NOT EXISTS pubchem_compounds_synonyms_all (cid int NOT NULL references pubchem_compounds_index(cid) ON UPDATE CASCADE ON DELET
• 
  Aaron Bertrand
  postgresql performance postgresql-9.2 group-by
  Also see http://stackoverflow.com/questions/17741167/hibernate-jpa-improve-performance-of-distinct-query but I realized this is mainly a PostgreSQL issue.My application uses a 3rd party extension to PostgreSQL to for searching chemical structures. This is in general slow. I can not change the SQL directly as the application uses hibernate and native query is not an option.I have a many-to-many relationship and the “Link-table” has an additional column. Basically I have a Mixture that consists of
• 
  Milovan Zogovic
  postgresql postgresql-9.2 heroku
  I have videos table with 18M rows. When I search for particular video by ID, it takes up to 6 seconds to complete. Sometimes it takes few milliseconds, sometimes up to 6 seconds, but on average it is around 2 seconds.Application is hosted on heroku and I’m using Crane database (https://addons.heroku.com/heroku-postgresql) with 410MB of RAM.Is there any way to speed this up? I’m querying for videos 50 times per second on average, and new videos are inserted/updated at rate of 50 per second.explai
• 
  mhenrixon
  postgresql postgresql-9.2
  So I have the following queryexplain analyze with tags as (select unnest(tags) as tag_name from tasks where user_id = 1 ) select count(9), tag_namefrom tagsgroup bytag_nameorder by count(9) desclimit 50Gives me the following result:Limit (cost=3243.86..3243.99 rows=50 width=32) (actual time=2.278..2.278 rows=1 loops=1)CTE tags-> Bitmap Heap Scan on tasks (cost=12.35..1917.72 rows=52700 width=13) (actual time=0.098..2.074 rows=261 loops=1)Recheck Cond: (user_id = 1)-> Bitmap Index Scan
• 
  Milovan Zogovic
  postgresql postgresql-9.2
  I have table with two indexes: latest_channel_snapshots_views_idx (view_count DESC NULLS LAST) latest_channel_snapshots_network_views_idx (network_id, view_count DESC NULLS LAST)What I want is to be able to sort by view_count overall, and within specific network. Postgres uses correct index for both of these cases. However, if i want to find record with most views where network_id is NULL, it uses the first index, and filters out the , thus performing really slow:explain analyze SELECT * FROM la
• 
  Timka
  postgresql security linux ubuntu postgresql-9.2
  After a fresh PostgreSQL install (version 9.2) on my Ubuntu server 12.04, clusterdb utility stopped working. I used the same config files as I used with version 9.1.Below is the actual command:clusterdb -v -h localhost -p <port#> -U postgres -d <dbname>And this is the response that I’m getting:clusterdb: could not connect to database <dbname>: could not connect to server: Connection refusedIs the server running on host “localhost” (127.0.0.1) and acceptingTCP/IP connections on
• 
  Craig Ringer
  postgresql derby postgresql-9.2
  In Derby (an embedded database written in Java which is mostly used for testing or prototyping) there are “freeze” and “unfreeze” commands which can be used during an online backup. “Freeze” simply causes all database accesses to block until “unfreeze” is called. This is useful for backing up using an external program, which you might do if the external program is much faster than using Derby’s internal backup solution. For my use case, I can take a snapshot almost instantaneously using some bui
• 
  orokusaki
  postgresql backup recovery postgresql-9.2 database-backup
  I’m reading http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html and to me it seems like it will remove any and all WAL segments from my slave server that aren’t required to perform recovery upon the slave server crashing (so basically everything except the last 1 or few segments, depending on how far behind my slave is running). I’d like to be able to perform emergency point in time recovery, in the unfortunate case of data corruption or accidental deletion, if possible. To do this,
• 
  orokusaki
  postgresql replication postgresql-9.2 failover master
  For starters, I do not have any sort of automated failover in place.After two scenarios, I’m unsure of the state of the database and any required actions, if any, to take:My master server disappears off the face of the planet and I touch my_trigger_file, converting my standby into a master server. My master server crashes, then restarts in recovery modeMy assumption is that the server in question in each of those scenarios will first be in recovery mode, will then finish recovering, and will fin
• 
  ajayjapan
  ruby-on-rails heroku postgis postgresql-9.2
  I am trying to deploy a rails application using activerecord-postgis-adapter to heroku, however I keep getting this error.ruby 2.0.0p0 (2013-02-24 revision 39474) [x86_64-darwin12.3.0]Rails 3.2.13$ heroku run rake db:schema:load Running `rake db:schema:load` attached to terminal… up, run.9233 DEPRECATION WARNING: You have Rails 2.3-style plugins in vendor/plugins! Support for these plugins will be removed in Rails 4.0. Move them out and bundle them in your Gemfile, or fold them in to your app
• 
  Oldskool
  postgresql gis postgis postgresql-9.1 postgresql-9.2
  I created a table named zipcode with the following columnsId State Zip Latitude Longitude column1 column2Columns named column1 and column2 are the 2 geometry columns i have created to store the lat and long in 2 formats -SRID 4269 (Lat/Lon) and SRID 2163 (US National Atlas – meters).Then i execute the following query to populate both the columns with the lat and lon dataUPDATE zipcode SET column1 = ST_SetSRID(ST_MakePoint(“longitude”,”latitude”),4269), column2 = ST_Transform(ST_SetSRID(ST_MakePo
• 
  kithril
  postgresql-9.2
  I’m porting old Oracle code to PostgreSQL 9.2. There are hundreds of calls to Oracle NVL(), so I created mapping functions to provide the equivalent functionality. This one should be trivial, but is failing for reasons not at all obvious to me.CREATE OR REPLACE FUNCTION nvl(expr1 text, expr2 text)RETURNS text AS $BODY$ BEGINRETURN COALESCE( expr1, expr2 ); END; $BODY$LANGUAGE plpgsql STRICT IMMUTABLE;I have variants of NVL defined for different input parameter types, but for testing, I’ve dele
• 
  Michal
  java database jdbc centos postgresql-9.2
  I know that many people ask this question but I try many path solution but any one was correct.So, I have problem with connect to my postgresql server(on CentOS 6.4 minimal version – I have there only postgresql server, ftp and ssh service) from remote client(in LAN). I have ssh connection so I think that problem can by with permission tcp/ip. Java throw exception:org.postgresql.util.PSQLException: Connection refused. Check that the hostname and port are correct and that the postmaster is accept
• 
  Dmitri Pisarenko
  postgresql postgis openstreetmap postgresql-9.2
  I want to import OSM file into my PostgreSQL database (Windows, Postgres Version 9.2) using the tool Osm2pgsql.When I run following commandosm2pgsql.exe –create -d mydb artyom.xml -U myuser -W –style default.styleI get the errorSELECT AddGeometryColumn(‘planet_osm_point’, ‘way’, 900913, ‘POINT’, 2 );failed: FEHLER: Funktion addgeometrycolumn(unknown, unknown, integer, unknown,integer) existiert nicht LINE 1: SELECT AddGeometryColumn(‘planet_osm_point’, ‘way’, 900913, …^ HINT: Keine Funktio
• 
  Dmitri Pisarenko
  postgresql postgis openstreetmap osm postgresql-9.2
  I’m trying to import one of the following OpenStreetMap maps 1, 2, 3 into a PostgreSQL database using calls like call osmosis.bat –read-xml file=”map.osm” –write-pgsimp user=”ccp-web-user” database=”ccp-web2″ password=”ccp-web-password”, but I always following error message.SCHWERWIEGEND: Thread for task 1-read-xml failed org.openstreetmap.osmosis.core.OsmosisRuntimeException: Unable to read the schema version from the schema info table.at org.openstreetmap.osmosis.pgsimple.common.SchemaVersio

Web site is in building