Re: [GENERAL] FATAL: the database system is starting up
fluxh wrote I have the same problem. I have a log like patrick keshishian. I have not a backup. I don't know that do. Help me please!!! Information: - Server with RAID1 on SAS HD Hot-plug. - Ubuntu Server 12.04 x64. - Postgresql 9.1. When I do psql PostgreSQL shows psql: FATAL: the database system is starting up and when I write pg_dump or I try connect with pgAdmin3. You should supply your actual log file and also provide some narrative of what exactly you were doing prior to this condition occurring. Some context as to architecture wouldn't hurt - such as how many and what kinds of applications are connecting to the database, especially those that maintain persistent connections. System commad output like top and ps may also be of value though after you provide the background a more directed QA can follow. In short it sounds like you need to force PostgreSQL to shutdown and then start it back up again. If you are using the apt package you'd do this via some invocation of pg_ctlcluster for which there is ample documentation of both it and the underlying pg_ctl command. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-the-database-system-is-starting-up-tp4941646p5795847.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] UNIQUE contraint that is initially deferred in Postgres 8.4.13
Hi list, I'm having trouble with a UNIQUE constraint that I need to have DEFERRABLE INITIALLY DEFERRED. On my Dev machine (Postgres 9.1) it works fine: alter table foo drop constraint bar; ALTER TABLE alter table foo add constraint bar UNIQUE (col1, col2) deferrable initially deferred; ALTER TABLE In Production (Postgres 8.4.13) this blows up in my face although it should be supported according to the docs: alter table foo drop constraint bar; ALTER TABLE alter table foo add constraint bar UNIQUE (col1, col2) deferrable initially deferred; FEHLER: Syntaxfehler bei »DEFERRABLE« LINE 1: bar UNIQUE (col1, col2) DEFERRABLE... ^ (i.e. syntax error at DEFERRABLE, psql seems to ignore my locale setting). Does somebody know what the reason for this could be? I'm kind of puzzled. Thanks in advance, Best regards, Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] UNIQUE contraint that is initially deferred in Postgres 8.4.13
On Thu, Mar 13, 2014 at 12:59 PM, Johannes Bauer dfnsonfsdu...@gmx.dewrote: Hi list, I'm having trouble with a UNIQUE constraint that I need to have DEFERRABLE INITIALLY DEFERRED. On my Dev machine (Postgres 9.1) it works fine: alter table foo drop constraint bar; ALTER TABLE alter table foo add constraint bar UNIQUE (col1, col2) deferrable initially deferred; ALTER TABLE In Production (Postgres 8.4.13) this blows up in my face although it should be supported according to the docs: alter table foo drop constraint bar; ALTER TABLE alter table foo add constraint bar UNIQUE (col1, col2) deferrable initially deferred; FEHLER: Syntaxfehler bei »DEFERRABLE« LINE 1: bar UNIQUE (col1, col2) DEFERRABLE... ^ (i.e. syntax error at DEFERRABLE, psql seems to ignore my locale setting). Does somebody know what the reason for this could be? I'm kind of puzzled. My guess is you're looking in the wrong place in the docs - can you specify where you are looking? If you are in the right place then the docs are wrong. Deferrable unique constraints are a new feature in PostgreSQL 9.0, so it shouldn't be working in 8.4. (And as a sidenote, a reminder that 8.4 will go end of life in just a couple of months, so if you aren't already planning an upgrade of your production environment, it's probably a good idea to start doing that, see http://www.postgresql.org/support/versioning/) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/
Re: [GENERAL] UNIQUE contraint that is initially deferred in Postgres 8.4.13
On 13.03.2014 13:02, Magnus Hagander wrote: My guess is you're looking in the wrong place in the docs - can you specify where you are looking? If you are in the right place then the docs are wrong. Here: http://www.postgresql.org/docs/8.4/static/sql-createtable.html Deferrable unique constraints are a new feature in PostgreSQL 9.0, so it shouldn't be working in 8.4. Ah! Okay, thanks for the info. That would explain it :-) (And as a sidenote, a reminder that 8.4 will go end of life in just a couple of months, so if you aren't already planning an upgrade of your production environment, it's probably a good idea to start doing that, see http://www.postgresql.org/support/versioning/) I'm going to roll up my sleeves and tackle that then... Thanks for the help, Joe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] FATAL: the database system is starting up
I have the same problem. I have a log like patrick keshishian. I have not a backup. I don't know that do. Help me please!!! Information: - Server with RAID1 on SAS HD Hot-plug. - Ubuntu Server 12.04 x64. - Postgresql 9.1. When I do psql PostgreSQL shows psql: FATAL: the database system is starting up and when I write pg_dump or I try connect with pgAdmin3. -- View this message in context: http://postgresql.1045698.n5.nabble.com/FATAL-the-database-system-is-starting-up-tp4941646p5795841.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] can't cast hstore to json
I've got a postgresql 9.3.2 server, compiled from scratch. Getting this
error:
# select hstore('a', 'b')::json;
ERROR: cannot cast type hstore to json
LINE 1: select hstore('a', 'b')::json;
^
# select array_to_json(array[hstore('a', 'b')]);
array_to_json
--
[\a\=\b\]
(1 row)
(I expected [{a: b}] )
I don't get this on other postgresql installations. Any ideas?
Joe
[GENERAL] puzzling perl DBI vs psql problem
I have a weird issue that I can't figure out. If I run the exact same query through psql as through perl DBI, I get different results. I get far fewer results with DBI than through the psql command line. Any ideas why that would be? The query is: SELECT st.description, st.scene_thing_instance_id, st.scene_id, sc.description, st.scene_thing_id, s.description, st.position_x, st.position_y, st.position_z, CASE when (st.description = 'absolute root'::text) then 1 when (st.description ilike 'root%') then 2 else 3 END as s1, s.shape_name_id, sn.shape_name from scene_thing_instances st left join scene_things s on st.scene_thing_id = s.scene_thing_id left join scenes sc on st.scene_id = sc.scene_id left outer join shape_names sn on s.shape_name_id = sn.shape_name_id order by s1, st.description I get 14 rows back via psql, but I only get 5 rows back via DBI. It's very puzzling. I copied and pasted the query from the program's log file, so I know I'm doing the exact same query. If it matters, I'm only seeing the rows with 'root' in them via DBI, which the CASE statement refers to. Thanks, Susan
Re: [GENERAL] can't cast hstore to json
Joe Van Dyk j...@tanga.com writes:
I've got a postgresql 9.3.2 server, compiled from scratch. Getting this
error:
# select hstore('a', 'b')::json;
ERROR: cannot cast type hstore to json
Probably need to update the hstore extension to 1.2.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] can't cast hstore to json
On Thu, Mar 13, 2014 at 12:15 PM, Joe Van Dyk j...@tanga.com wrote:
I've got a postgresql 9.3.2 server, compiled from scratch. Getting this
error:
# select hstore('a', 'b')::json;
ERROR: cannot cast type hstore to json
LINE 1: select hstore('a', 'b')::json;
^
# select array_to_json(array[hstore('a', 'b')]);
array_to_json
--
[\a\=\b\]
(1 row)
(I expected [{a: b}] )
I don't get this on other postgresql installations. Any ideas?
Joe
ALTER EXTENSION hstore UPDATE; fixed the problem. (thanks oicu!)
Re: [GENERAL] puzzling perl DBI vs psql problem
Susan Cassidy susan.cass...@decisionsciencescorp.com writes: I have a weird issue that I can't figure out. If I run the exact same query through psql as through perl DBI, I get different results. I get far fewer results with DBI than through the psql command line. Any possibility that the perl program is connecting to a different database, with similar-but-not-identical data in it? I've seen variants on that theme involving different users with different search_path settings finding different-but-similarly-named tables in different schemas of the same database. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Very slow query in PostgreSQL 9.3.3
PostgreSQL 9.3.3 RHEL 6.4Total db Server memory 64GB# -# PostgreSQL configuration file# -max_connections = 100shared_buffers = 16GBwork_mem = 32MB maintenance_work_mem = 1GBseq_page_cost = 1.0 random_page_cost = 2.0 cpu_tuple_cost = 0.03 #cpu_index_tuple_cost = 0.005 #cpu_operator_cost = 0.0025 effective_cache_size = 48MBdefault_statistics_target = 100 constraint_exclusion = partition Partition table Setup-CREATE TABLE measurement ( id bigint not null, city_id bigint not null, logdate date not null, peaktemp bigint, unitsales bigint, type bigint, uuid uuid, geom geometry);CREATE TABLE measurement_y2006m02 ( CHECK ( logdate = DATE '2006-02-01' AND logdate DATE '2006-03-01' )) INHERITS (measurement);CREATE TABLE measurement_y2006m03 ( CHECK ( logdate = DATE '2006-03-01' AND logdate DATE '2006-04-01' )) INHERITS (measurement);...CREATE TABLE measurement_y2007m11 ( CHECK ( logdate = DATE '2007-11-01' AND logdate DATE '2007-12-01' )) INHERITS (measurement);CREATE TABLE measurement_y2007m12 ( CHECK ( logdate = DATE '2007-12-01' AND logdate DATE '2008-01-01' )) INHERITS (measurement);CREATE TABLE measurement_y2008m01 ( CHECK ( logdate = DATE '2008-01-01' AND logdate DATE '2008-02-01' )) INHERITS (measurement);Partition measurement_y2007m12 contains 38,261,732 rowsIndexes on partition measurement_y2007m12: "pkey_measurement_y2007m12" PRIMARY KEY, btree (id), tablespace "measurement_y2007" "idx_measurement_uuid_y2003m12" btree (uuid), tablespace "measurement_y2007" "idx_measurement_type_y2003m12" btree (type), tablespace "measurement_y2007" "idx_measurement_city_y2003m12" btree (city_id), tablespace "measurement_y2007" "idx_measurement_logdate_y2003m12" btree (logdate), tablespace "measurement_y2007" "sidx_measurement_geom_y2003m12" gist (geom), tablespace "measurement_y2007"*** Problem Query *** explain (analyze on, buffers on) Select * from measurement this_ where this_.logdate between '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp and this_.city_id=25183 order by this_.logdate asc, this_.peaktemp asc, this_.unitsales asc limit 1; QUERY PLAN ---Limit (cost=33849.98..33855.15 rows=2068 width=618) (actual time=51710.803..51714.266 rows=1 loops=1) Buffers: shared hit=25614 read=39417 - Sort (cost=33849.98..33855.15 rows=2068 width=618) (actual time=51710.799..51712.924 rows=1 loops=1) Sort Key: this_.logdate, this_.unitsales Sort Method: top-N heapsort Memory: 15938kB Buffers: shared hit=25614 read=39417 - Append (cost=0.00..33736.09 rows=2068 width=618) (actual time=50.210..50793.589 rows=312046 loops=1) Buffers: shared hit=25608 read=39417 - Seq Scan on measurement this_ (cost=0.00..0.00 rows=1 width=840) (actual time=0.002..0.002 rows=0 loops=1) Filter: ((logdate = '2007-12-19 23:38:41.22'::timestamp without time zone) AND (logdate = '2007-12-20 08:01:04.22'::timestamp without time zone) AND (city_id = 25183)) - Index Scan using idx_measurement_city_y2007m12 on measurement_y2007m12 this__1 (cost=0.56..33736.09 rows=2067 width=618) (actual time=50.206..50731.637 rows=312046 loops=1) Index Cond: (city_id = 25183) Filter: ((logdate = '2007-12-19 23:38:41.22'::timestamp without time zone) AND (logdate = '2007-12-20 08:01:04.22'::timestamp without time zone)) Buffers: shared hit=25608 read=39417Total runtime: 51717.639 ms --- *** unacceptable ***(15 rows) Total Rows meeting query criteria-Select count(*) from measurement this_ where this_.logdate between '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp and this_.city_id=25183;count--312046Total Rows in the partition table referenced--Select count(*) from measurement_y2007m12; count-38261732Does anyone know how to speed up this query? I removed the order by clause and that significantly reduced the run time to approx. 2000-3000 ms. This query is being recorded repeatedly in our logs and executes very slowly for our UI users from 12000 ms thru 68000 msAny suggestions would be appreciated.thanks
Re: [GENERAL] puzzling perl DBI vs psql problem
On Mar 13, 2014, at 12:18 PM, Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: I copied and pasted the query from the program's log file, so I know I'm doing the exact same query. If it matters, I'm only seeing the rows with 'root' in them via DBI, which the CASE statement refers to. Try enabling full query logging on postgres to see what query is actually running both times. %' shouldn't resolve to anything in perl, but there could be other issues with misquoting the query. ~ john -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [BUGS] Very slow query in PostgreSQL 9.3.3
2014-03-13 20:26 GMT+01:00 fburg...@radiantblue.com: PostgreSQL 9.3.3 RHEL 6.4 Total db Server memory 64GB # - # PostgreSQL configuration file # - max_connections = 100 shared_buffers = 16GB work_mem = 32MB maintenance_work_mem = 1GB seq_page_cost = 1.0 random_page_cost = 2.0 cpu_tuple_cost = 0.03 #cpu_index_tuple_cost = 0.005 #cpu_operator_cost = 0.0025 effective_cache_size = 48MB default_statistics_target = 100 constraint_exclusion = partition Partition table Setup - CREATE TABLE measurement ( id bigint not null, city_id bigint not null, logdate date not null, peaktempbigint, unitsales bigint, typebigint, uuiduuid, geomgeometry ); CREATE TABLE measurement_y2006m02 ( CHECK ( logdate = DATE '2006-02-01' AND logdate DATE '2006-03-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2006m03 ( CHECK ( logdate = DATE '2006-03-01' AND logdate DATE '2006-04-01' ) ) INHERITS (measurement); ... CREATE TABLE measurement_y2007m11 ( CHECK ( logdate = DATE '2007-11-01' AND logdate DATE '2007-12-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2007m12 ( CHECK ( logdate = DATE '2007-12-01' AND logdate DATE '2008-01-01' ) ) INHERITS (measurement); CREATE TABLE measurement_y2008m01 ( CHECK ( logdate = DATE '2008-01-01' AND logdate DATE '2008-02-01' ) ) INHERITS (measurement); Partition measurement_y2007m12 contains 38,261,732 rows Indexes on partition measurement_y2007m12: pkey_measurement_y2007m12 PRIMARY KEY, btree (id), tablespace measurement_y2007 idx_measurement_uuid_y2003m12 btree (uuid), tablespace measurement_y2007 idx_measurement_type_y2003m12 btree (type), tablespace measurement_y2007 idx_measurement_city_y2003m12 btree (city_id), tablespace measurement_y2007 idx_measurement_logdate_y2003m12 btree (logdate), tablespace measurement_y2007 sidx_measurement_geom_y2003m12 gist (geom), tablespace measurement_y2007 Problem Query *** * explain (analyze on, buffers on) Select * from measurement this_ where this_.logdate between '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp and this_.city_id=25183 order by this_.logdate asc, this_.peaktemp asc, this_.unitsales asc limit 1; QUERY PLAN --- Limit (cost=33849.98..33855.15 rows=2068 width=618) (actual time=51710.803..51714.266 rows=1 loops=1) Buffers: shared hit=25614 read=39417 - Sort (cost=33849.98..33855.15 rows=2068 width=618) (actual time=51710.799..51712.924 rows=1 loops=1) Sort Key: this_.logdate, this_.unitsales Sort Method: top-N heapsort Memory: 15938kB Buffers: shared hit=25614 read=39417 - Append (cost=0.00..33736.09 rows=2068 width=618) (actual time=50.210..50793.589 rows=312046 loops=1) Buffers: shared hit=25608 read=39417 - Seq Scan on measurement this_ (cost=0.00..0.00 rows=1 width=840) (actual time=0.002..0.002 rows=0 loops=1) Filter: ((logdate = '2007-12-19 23:38:41.22'::timestamp without time zone) AND (logdate = '2007-12-20 08:01:04.22'::timestamp without time zone) AND (city_id = 25183)) - Index Scan using idx_measurement_city_y2007m12 on measurement_y2007m12 this__1 (cost=0.56..33736.09 rows=2067 width=618) (actual time=50.206..50731.637 rows=312046 loops=1) Index Cond: (city_id = 25183) Filter: ((logdate = '2007-12-19 23:38:41.22'::timestamp without time zone) AND (logdate = '2007-12-20 08:01:04.22'::timestamp without time zone)) Buffers: shared hit=25608 read=39417 Total runtime: *51717.639 ms* --- *** unacceptable *** (15 rows) Total Rows meeting query criteria - Select count(*) from measurement this_ where this_.logdate between '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp and this_.city_id=25183; count -- 312046 Total Rows in the partition table referenced -- Select count(*) from measurement_y2007m12; count - 38261732 *Does anyone know how to speed up this query? I removed the order by clause and that significantly reduced the run time to approx. 2000-3000 ms. This query is being recorded repeatedly in our logs and executes very slowly for our UI users from 12000 ms thru 68000 msAny suggestions would be appreciated.* sort (ORDER BY clause) enforce a reading of
Re: [GENERAL] puzzling perl DBI vs psql problem
On Mar 13, 2014, at 12:18 PM, Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: I have a weird issue that I can't figure out. If I run the exact same query through psql as through perl DBI, I get different results. I get far fewer results with DBI than through the psql command line. Any ideas why that would be? The query is: SELECT st.description, st.scene_thing_instance_id, st.scene_id, sc.description, st.scene_thing_id, s.description, st.position_x, st.position_y, st.position_z, CASE when (st.description = 'absolute root'::text) then 1 when (st.description ilike 'root%') then 2 else 3 END as s1, s.shape_name_id, sn.shape_name from scene_thing_instances st left join scene_things s on st.scene_thing_id = s.scene_thing_id left join scenes sc on st.scene_id = sc.scene_id left outer join shape_names sn on s.shape_name_id = sn.shape_name_id order by s1, st.description I get 14 rows back via psql, but I only get 5 rows back via DBI. It's very puzzling. I copied and pasted the query from the program's log file, so I know I'm doing the exact same query. If it matters, I'm only seeing the rows with 'root' in them via DBI, which the CASE statement refers to. How are you quoting the string in perl, and are you running with use strict? My first thought would be that you’re not running the query you think you are - logging it at the postgresql side will let you check that (or if that’s not possible, DBI’s trace methods can help). Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] puzzling perl DBI vs psql problem
Yes, I am running with use strict. The statement I pasted in is after perl quoting, being written out by the same perl program. I just take that statement and paste it into the psql window. DBI-trace showed nothing out of the ordinary. It just shows the lines being fetched that I am seeing in the web program, not the lines I get from psql. Another odd thing is that it is apparently not logging statements from Perl, only from psql. I don't know why. I thought I had it set up right to log to syslog. I've had good luck before with that on other installations. Here are the log settings in postgresql.conf: log_destination = 'syslog' # Valid values are combinations of # stderr, csvlog, syslog, and eventlog, # depending on platform. csvlog # requires logging_collector to be on. # This is used when logging to stderr: logging_collector = on # Enable capturing of stderr and csvlog #logging_collector = off# Enable capturing of stderr and csvlog # into log files. Required to be on for # csvlogs. # These are only used if logging_collector is on: log_directory = 'pg_log'# directory where log files are written, log_filename = 'postgresql-%a.log' # log file name pattern, #log_file_mode = 0600 # creation mode for log files, log_truncate_on_rotation = on # If on, an existing log file with the # same name as the new log file will be log_rotation_age = 1d # Automatic rotation of logfiles will log_rotation_size = 0 # Automatic rotation of logfiles will # happen after that much log output. # These are relevant when logging to syslog: syslog_facility = 'LOCAL0' syslog_ident = 'postgres' # This is only relevant when logging to eventlog (win32): client_min_messages = log # values in order of decreasing detail: # log log_min_messages = info # values in order of decreasing detail: #log_min_messages = warning # values in order of decreasing detail: # log #log_min_error_statement = error# values in order of decreasing detail: # log #log_min_duration_statement = -1# -1 is disabled, 0 logs all statements # and their durations, 0 logs only #log_checkpoints = off #log_connections = off #log_disconnections = off #log_duration = off #log_error_verbosity = default # terse, default, or verbose messages #log_hostname = off log_line_prefix = '%d %u %p %t '# special values: Could it have something to do with permissions on /var/log/postgresql? It is writeable by root only. The perl program runs under apache. Susan On Thu, Mar 13, 2014 at 12:46 PM, Steve Atkins st...@blighty.com wrote: On Mar 13, 2014, at 12:18 PM, Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: I have a weird issue that I can't figure out. If I run the exact same query through psql as through perl DBI, I get different results. I get far fewer results with DBI than through the psql command line. Any ideas why that would be? The query is: SELECT st.description, st.scene_thing_instance_id, st.scene_id, sc.description, st.scene_thing_id, s.description, st.position_x, st.position_y, st.position_z, CASE when (st.description = 'absolute root'::text) then 1 when (st.description ilike 'root%') then 2 else 3 END as s1, s.shape_name_id, sn.shape_name from scene_thing_instances st left join scene_things s on st.scene_thing_id = s.scene_thing_id left join scenes sc on st.scene_id = sc.scene_id left outer join shape_names sn on s.shape_name_id = sn.shape_name_id order by s1, st.description I get 14 rows back via psql, but I only get 5 rows back via DBI. It's very puzzling. I copied and pasted the query from the program's log file, so I know I'm doing the exact same query. If it matters, I'm only seeing the rows with 'root' in them via DBI, which the CASE statement refers to. How are you quoting the string in perl, and are you running with use strict? My first thought would be that you're not running the query you think you are - logging it at the postgresql side will let you check that (or if that's not possible, DBI's trace methods can help). Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] puzzling perl DBI vs psql problem
No, I am connecting to the right database, as my log info shows. No duplicate table names, except in different databases. Those tables show all the data I expect, oddly enough, if I connect the perl program to that database. It is only this database that is giving me trouble. Which is another oddity. The identical test database is working fine. Only this database is giving me trouble. Naturally the live database is causing problems. Susan On Thu, Mar 13, 2014 at 12:25 PM, Tom Lane t...@sss.pgh.pa.us wrote: Susan Cassidy susan.cass...@decisionsciencescorp.com writes: I have a weird issue that I can't figure out. If I run the exact same query through psql as through perl DBI, I get different results. I get far fewer results with DBI than through the psql command line. Any possibility that the perl program is connecting to a different database, with similar-but-not-identical data in it? I've seen variants on that theme involving different users with different search_path settings finding different-but-similarly-named tables in different schemas of the same database. regards, tom lane
Re: [GENERAL] puzzling perl DBI vs psql problem
On Mar 13, 2014, at 1:20 PM, Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: Yes, I am running with use strict. The statement I pasted in is after perl quoting, being written out by the same perl program. I just take that statement and paste it into the psql window. DBI-trace showed nothing out of the ordinary. It just shows the lines being fetched that I am seeing in the web program, not the lines I get from psql. Another odd thing is that it is apparently not logging statements from Perl, only from psql. I don't know why. I thought I had it set up right to log to syslog. I've had good luck before with that on other installations. That’s supports Tom’s theory that you’re not connecting to the database you think you are. There’s no difference from the database’s PoV between queries from psql and perl. Conceivably connecting as different users could change the result too, though it seems unlikely here. Here are the log settings in postgresql.conf: [fairly normal settings deleted] Could it have something to do with permissions on /var/log/postgresql? It is writeable by root only. The perl program runs under apache. No, that file is written by syslog, nothing to do with the client. If the perl is running under apache, though, is it possible that it’s a long-running process that’s kept a transaction open and is seeing old data? Bounce apache and see if anything changes. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] puzzling perl DBI vs psql problem
On Thu, 13 Mar 2014 13:20:53 -0700 Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: Another odd thing is that it is apparently not logging statements from Perl, only from psql. I don't know why. I thought I had it set up right to log to syslog. I've had good luck before with that on other installations. I can bet that it is connecting to another database or server, check your perl script configuration. Best regards Rodrigo Gonzalez -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] puzzling perl DBI vs psql problem
No, it is connecting to localhost, which is the same system I am running psql on. Susan On Thu, Mar 13, 2014 at 1:26 PM, Rodrigo Gonzalez rjgonzale.li...@gmail.com wrote: On Thu, 13 Mar 2014 13:20:53 -0700 Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: Another odd thing is that it is apparently not logging statements from Perl, only from psql. I don't know why. I thought I had it set up right to log to syslog. I've had good luck before with that on other installations. I can bet that it is connecting to another database or server, check your perl script configuration. Best regards Rodrigo Gonzalez
Re: [GENERAL] puzzling perl DBI vs psql problem
On Thu, 13 Mar 2014 13:28:38 -0700 Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: No, it is connecting to localhost, which is the same system I am running psql on. Susan Well, if one query is logged and the other one is not it means that it is running against different servers (as far as I understand logging) Maybe psql is connecting using one socket and perl using another one? maybe you have 2 instances running? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] puzzling perl DBI vs psql problem
No, I don't have 2 instances running. I default the port on the psql command line, and the perl program is using 5432, as normal. Now, I'm discovering that syslog is no longer logging anything. I bounced it, but to no avail. Susan On Thu, Mar 13, 2014 at 1:34 PM, Rodrigo Gonzalez rjgonzale.li...@gmail.com wrote: On Thu, 13 Mar 2014 13:28:38 -0700 Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: No, it is connecting to localhost, which is the same system I am running psql on. Susan Well, if one query is logged and the other one is not it means that it is running against different servers (as far as I understand logging) Maybe psql is connecting using one socket and perl using another one? maybe you have 2 instances running?
Re: [GENERAL] puzzling perl DBI vs psql problem
1) Run both psql and perl under strace and search the output for which sockets it connects to. eg, strace -o /tmp/psql.log psql -Upgsql -dmydatabase -cselect version(); 2) Add a query into your perl script to perform the following SQL and print the results: select current_database(); select current_schema(); select inet_server_addr(); select current_user; (and others, see http://www.postgresql.org/docs/9.3/static/functions-info.html for more functions) Do the same from your psql. Compare the output. Are you 110% sure that you are connecting to the same database, as the same user, and using the same schema? On Thu, Mar 13, 2014 at 3:44 PM, Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: No, I don't have 2 instances running. I default the port on the psql command line, and the perl program is using 5432, as normal. Now, I'm discovering that syslog is no longer logging anything. I bounced it, but to no avail. Susan On Thu, Mar 13, 2014 at 1:34 PM, Rodrigo Gonzalez rjgonzale.li...@gmail.com wrote: On Thu, 13 Mar 2014 13:28:38 -0700 Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: No, it is connecting to localhost, which is the same system I am running psql on. Susan Well, if one query is logged and the other one is not it means that it is running against different servers (as far as I understand logging) Maybe psql is connecting using one socket and perl using another one? maybe you have 2 instances running?
Re: [GENERAL] puzzling perl DBI vs psql problem
On Thu, 13 Mar 2014 13:44:48 -0700 Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: No, I don't have 2 instances running. I default the port on the psql command line, and the perl program is using 5432, as normal. Now, I'm discovering that syslog is no longer logging anything. I bounced it, but to no avail. That is other problemtry changing pgsql to use stderr (or fix syslog of course)...then check both queries again and see the differences... Other good point by other user is that maybe you are using persistent connections from apache and it is using an old snapshot? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] puzzling perl DBI vs psql problem
On 03/13/2014 02:44 PM, Susan Cassidy wrote: No, I don't have 2 instances running. I default the port on the psql command line, and the perl program is using 5432, as normal. Now, I'm discovering that syslog is no longer logging anything. I bounced it, but to no avail. Susan On Thu, Mar 13, 2014 at 1:34 PM, Rodrigo Gonzalez rjgonzale.li...@gmail.com mailto:rjgonzale.li...@gmail.com wrote: On Thu, 13 Mar 2014 13:28:38 -0700 Susan Cassidy susan.cass...@decisionsciencescorp.com mailto:susan.cass...@decisionsciencescorp.com wrote: No, it is connecting to localhost, which is the same system I am running psql on. Susan Well, if one query is logged and the other one is not it means that it is running against different servers (as far as I understand logging) Maybe psql is connecting using one socket and perl using another one? maybe you have 2 instances running? I would like to see the output of ps auxw | grep p when each or both is running
Re: [GENERAL] puzzling perl DBI vs psql problem
You asked for it (by the way, just p is asking for a lot of output): root23 0.0 0.0 0 0 ?SFeb20 0:00 [cgroup] root24 0.0 0.0 0 0 ?SFeb20 0:00 [khelper] root27 0.0 0.0 0 0 ?SFeb20 0:00 [pm] root28 0.0 0.0 0 0 ?SFeb20 0:05 [sync_supers] root38 0.0 0.0 0 0 ?SFeb20 0:00 [kacpid] root39 0.0 0.0 0 0 ?SFeb20 0:00 [kacpi_notify] root40 0.0 0.0 0 0 ?SFeb20 0:00 [kacpi_hotplug] root46 0.0 0.0 0 0 ?SFeb20 0:00 [ksuspend_usbd] root59 0.0 0.0 0 0 ?SFeb20 0:21 [kswapd0] root61 0.0 0.0 0 0 ?SN Feb20 1:26 [khugepaged] root66 0.0 0.0 0 0 ?SFeb20 0:00 [crypto/0] root67 0.0 0.0 0 0 ?SFeb20 0:00 [crypto/1] root68 0.0 0.0 0 0 ?SFeb20 0:00 [crypto/2] root69 0.0 0.0 0 0 ?SFeb20 0:00 [crypto/3] root79 0.0 0.0 0 0 ?SFeb20 0:00 [kpsmoused] root 111 0.0 0.0 0 0 ?SFeb20 0:00 [kstriped] root 447 0.0 0.0 0 0 ?SFeb20 0:10 [kcryptd_io] root 448 0.0 0.0 0 0 ?SFeb20 8:03 [kcryptd] rpc 1985 0.0 0.0 18976 336 ?Ss Feb20 0:01 rpcbind root 2000 0.0 0.0 192216 1508 ?SFeb20 0:14 /usr/libexec/sssd/sssd_pam --debug-to-files root 2003 0.0 0.0 210388 632 ?SFeb20 0:10 /usr/libexec/sssd/sssd_pac --debug-to-files root 3794 0.0 0.0 92400 3396 ?Ssl Feb20 0:03 NetworkManager --pid-file=/var/run/NetworkManager/NetworkManager.pid rpcuser 3815 0.0 0.0 25424 320 ?Ss Feb20 0:00 rpc.statd root 3830 0.0 0.0 9120 664 ?SFeb20 0:17 /sbin/dhclient -d -4 -sf /usr/libexec/nm-dhcp-client.action -pf /var/run/dhclient-eth0.pid -lf /var/lib/dhclient/dhclient-90769195-b652-4a56-b37d-60b98c4c86d5-eth0.lease -cf /var/run/nm-dhclient-eth0.conf eth0 root 3837 0.0 0.0 44972 192 ?Ss Feb20 0:00 /usr/sbin/wpa_supplicant -c /etc/wpa_supplicant/wpa_supplicant.conf -B -u -f /var/log/wpa_supplicant.log -P /var/run/wpa_supplicant.pid root 3855 0.0 0.0 0 0 ?SFeb20 0:00 [rpciod/0] root 3856 0.0 0.0 0 0 ?SFeb20 0:00 [rpciod/1] root 3857 0.0 0.0 0 0 ?SFeb20 0:00 [rpciod/2] root 3858 0.0 0.0 0 0 ?SFeb20 0:00 [rpciod/3] root 3862 0.0 0.0 25164 208 ?Ss Feb20 0:00 rpc.idmapd root 3877 0.0 0.0 200440 1844 ?Ss Feb20 0:05 cupsd -C /etc/cups/cupsd.conf root 3907 0.0 0.0 4080 192 ?Ss Feb20 0:00 /usr/sbin/acpid root 3963 0.0 0.0 22448 448 ?SFeb20 0:22 hald-addon-input: Listening on /dev/input/event4 /dev/input/event0 /dev/input/event1 /dev/input/event5 683966 0.0 0.0 17936 272 ?SFeb20 0:00 hald-addon-acpi: listening on acpid socket /var/run/acpid.socket ntp 4021 0.0 0.0 32520 668 ?Ss Feb20 0:57 ntpd -x -u ntp:ntp -p /var/run/ntpd.pid root 4102 0.0 0.0 83312 440 ?Ss Feb20 0:04 /usr/libexec/postfix/master postfix 4123 0.0 0.0 83564 404 ?SFeb20 0:00 qmgr -l -t fifo -u root 4146 0.0 0.0 23032 120 ?Ss Feb20 0:00 /usr/sbin/oddjobd -p /var/run/oddjobd.pid -t 300 root 4161 0.0 0.0 62296 592 ?Ss Feb20 0:01 /usr/sbin/certmonger -S -p /var/run/certmonger.pid root 4337 0.0 0.0 49876 992 ?SFeb20 0:00 /usr/libexec/devkit-power-daemon root 4378 0.0 0.0 195196 1908 ?SFeb20 0:01 /usr/libexec/polkit-1/polkitd root 4560 0.0 0.0 45108 248 ?SFeb20 0:00 udisks-daemon: not polling any devices scassidy 4651 0.0 0.0 73044 4128 pts/4S+ Mar12 0:00 ssh mrhankey pwalters 4774 0.0 0.0 804604 640 ?Sl Feb20 0:00 /usr/libexec/e-calendar-factory root 4825 0.0 0.0 173480 648 ?Sl Feb20 0:00 /usr/libexec/gdm-simple-slave --display-id /org/gnome/DisplayManager/Display1 root 4827 2.5 2.7 625704 220620 tty7Ss+ Feb20 787:34 /usr/bin/Xorg :0 -br -verbose -audit 4 -auth /var/run/gdm/auth-for-gdm-YFiIyA/database -nolisten tcp root 4883 0.0 0.0 204840 556 ?Sl Feb20 0:00 pam: gdm-password scassidy 4912 0.0 0.0 31028 1476 ?Ss Feb20 0:02 /bin/dbus-daemon --fork --print-pid 6 --print-address 8 --session scassidy 5022 0.1 0.0 450860 3912 ?Ssl Feb20 33:13 /usr/bin/pulseaudio --start --log-target=syslog scassidy 5023 0.0 0.1 358976 11188 ?SFeb20 0:57 gnome-panel scassidy 5026 0.0 0.0
Re: [GENERAL] puzzling perl DBI vs psql problem
On 03/13/2014 03:23 PM, Susan Cassidy wrote: The only one that comes out different is inet_server_addr, via the program, it comes out: ::1 whereas via psql it comes out empty. Yes, I am 100% sure I am using the same schema (which I never specify, so I am using 'public') and the same user and database. Susan Unless username is also a schema name, then you're in that schema. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] puzzling perl DBI vs psql problem
I finally figured it out, after changing my code to output the lines per the number of rows of output, instead of until data[0] was blank. It turned out that data[0] was sometimes blank, and I forgot about that, and was stopping the output after I got back an empty record (or so I thought). So, all my fault. The syslog thing I fixed by changing log_min_duration to 0, instead of letting it default. I don't think this used to be the default (to not log any statements). Thanks for all the ideas, anyway, folks. Thanks again, Susan On Thu, Mar 13, 2014 at 2:27 PM, Rob Sargent robjsarg...@gmail.com wrote: On 03/13/2014 03:23 PM, Susan Cassidy wrote: The only one that comes out different is inet_server_addr, via the program, it comes out: ::1 whereas via psql it comes out empty. Yes, I am 100% sure I am using the same schema (which I never specify, so I am using 'public') and the same user and database. Susan Unless username is also a schema name, then you're in that schema. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] puzzling perl DBI vs psql problem
The only one that comes out different is inet_server_addr, via the program, it comes out: ::1 whereas via psql it comes out empty. Yes, I am 100% sure I am using the same schema (which I never specify, so I am using 'public') and the same user and database. Susan On Thu, Mar 13, 2014 at 1:53 PM, Dennis Jenkins dennis.jenkins...@gmail.com wrote: 1) Run both psql and perl under strace and search the output for which sockets it connects to. eg, strace -o /tmp/psql.log psql -Upgsql -dmydatabase -cselect version(); 2) Add a query into your perl script to perform the following SQL and print the results: select current_database(); select current_schema(); select inet_server_addr(); select current_user; (and others, see http://www.postgresql.org/docs/9.3/static/functions-info.html for more functions) Do the same from your psql. Compare the output. Are you 110% sure that you are connecting to the same database, as the same user, and using the same schema? On Thu, Mar 13, 2014 at 3:44 PM, Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: No, I don't have 2 instances running. I default the port on the psql command line, and the perl program is using 5432, as normal. Now, I'm discovering that syslog is no longer logging anything. I bounced it, but to no avail. Susan On Thu, Mar 13, 2014 at 1:34 PM, Rodrigo Gonzalez rjgonzale.li...@gmail.com wrote: On Thu, 13 Mar 2014 13:28:38 -0700 Susan Cassidy susan.cass...@decisionsciencescorp.com wrote: No, it is connecting to localhost, which is the same system I am running psql on. Susan Well, if one query is logged and the other one is not it means that it is running against different servers (as far as I understand logging) Maybe psql is connecting using one socket and perl using another one? maybe you have 2 instances running?
Re: [GENERAL] [BUGS] Very slow query in PostgreSQL 9.3.3
On Thu, Mar 13, 2014 at 12:26 PM, fburg...@radiantblue.com wrote: *** Problem Query *** explain (analyze on, buffers on) Select * from measurement this_ where this_.logdate between '2007-12-19 23:38:41.22'::timestamp and '2007-12-20 08:01:04.22'::timestamp and this_.city_id=25183 order by this_.logdate asc, this_.peaktemp asc, this_.unitsales asc limit 1; [...] Total runtime: 51717.639 ms --- *** unacceptable *** Try to create a multi-column index on the partition by (city_id, logdate). Then run the original query and the query without peaktemp and nitsales on the order by. Compare the results, and if the first one will not be satisfying try to add these two columns to the end of the column list of your multi-column index on the order as they appear in your query. It should do the trick. If it wont, please, show the plans. -- Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (901) 903-0499, +7 (988) 888-1979 gray...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] [PERFORM] Very slow query in PostgreSQL 9.3.3
2014-03-14 4:26 GMT+09:00 fburg...@radiantblue.com: PostgreSQL 9.3.3 RHEL 6.4 Total db Server memory 64GB (...) effective_cache_size = 48MB I'm not sure if this will help directly, but is the value for 'effective_cache_size' intentional? 48 *GB* would be a more likely setting. Regards Ian Barwick -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
