[GENERAL] Install PostgreSQL 9.2.4 to IBM Power System ppc64
Hi, I have two IBM Power System servers, the architecture is ppc64, the PostgreSQL on RHEL6 for IBM Power is version 8.4 but I need to install 9.2.4 as I need to use the PostgreSQL replication features. I am new to PostgreSQL on ppc64, can any one advise me where to get the proper 9.2.4 installation package for ppc64? or please advise me the steps to compile from the 9.2.4 source. Regards -- 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] Install PostgreSQL 9.2.4 to IBM Power System ppc64
I have two IBM Power System servers, the architecture is ppc64, the PostgreSQL on RHEL6 for IBM Power is version 8.4 but I need to install 9.2.4 as I need to use the PostgreSQL replication features. I am new to PostgreSQL on ppc64, can any one advise me where to get the proper 9.2.4 installation package for ppc64? or please advise me the steps to compile from the 9.2.4 source. I think there's nothing special with installing PostgreSQL from 9.2.4. source code on Power. Please let me know if you have any problem. -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php Japanese: http://www.sraoss.co.jp -- 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] Backup advice
On Mon, 15 Apr 2013 19:54:15 -0700
Jeff Janes jeff.ja...@gmail.com wrote:
On Tue, Apr 9, 2013 at 3:05 AM, Eduardo Morras
emorr...@yahoo.esjavascript:_e({}, 'cvml', 'emorr...@yahoo.es');
wrote:
On Mon, 8 Apr 2013 10:40:16 -0500
Shaun Thomas stho...@optionshouse.com javascript:_e({}, 'cvml',
'stho...@optionshouse.com'); wrote:
Anyone else?
If his db has low inserts/updates/deletes he can use diff between pg_dumps
(with default -Fp) before compressing.
Most diff implementations will read the entirety of both files into
memory, so may not work well with 200GB of data, unless it is broken into a
large number of much smaller files.
open-vcdiff only reads one of the files into memory, but I couldn't really
figure out what happens memory-wise when you try to undo the resulting
patch, the documentation is a bit mysterious.
xdelta3 will work on streamed files of unlimited size, but it doesn't
work very well unless the files fit in memory, or have the analogous data
in the same order between the two files.
I use for my 12-13 GB dump files:
git diff -p 1.sql 2.sql diff.patch
It uses 4MB for firts phase and upto 140MB on last one and makes a patch file
that can be recovered with:
patch 1.sql diff.patch 2.sql
or using git apply.
A while ago I did some attempts to co-compress dump files, based on the
notion that the pg_dump text format does not have \n within records so it
is sortable as ordinary text, and that usually tables have their stable
columns, like a pk, near the beginning of the table and volatile columns
near the end, so that sorting the lines of several dump files together will
gather replicate or near-replicate lines together where ordinary
compression algorithms can work their magic. So if you tag each line with
its line number and which file it originally came from, then sort the lines
(skipping the tag), you get much better compression. But not nearly as
good as open-vcdiff, assuming you have the RAM to spare.
Using two dumps taken months apart on a slowly-changing database, it worked
fairly well:
cat 1.sql | pigz |wc -c
329833147
cat 2.sql | pigz |wc -c
353716759
cat 1.sql 2.sql | pigz |wc -c
683548147
sort -k2 (perl -lne 'print ${.}a\t$_' 1.sql) (perl -lne 'print
${.}b\t$_' 2.sql) | pigz |wc -c
436350774
A certain file could be recovered by, for example:
zcat group_compressed.gz |sort -n|perl -lne 's/^(\d+b\t)// and print'
2.sql2
Be careful, some z* utils decompress the whole file on /tmp (zdiff).
There all kinds of short-comings here, of course, it was just a quick and
dirty proof of concept.
A nice one !
For now I think storage is cheap enough for what I need to do to make this
not worth fleshing it out any more.
Cheers,
Jeff
--- ---
Eduardo Morras emorr...@yahoo.es
--
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] Role Authentication Failure
On 04/15/2013 07:29 PM, Carlos Mennens wrote: I dropped both roles (Carlos DBA) from the database and I will show you exactly what I'm doing: Now I login as 'carlos': carlos@debian:~$ psql -d postgres Password: psql (9.1.9) Type help for help. postgres= but... To add to my post from last night: 1) What is in your pg_hba.conf file? 2) When you do psql -d postgres are you sure you are logging in as carlos? In other words no PGUSER set. While debugging this I would try to be as explicit as possible in your command line 3) Do you have a .pgpass file and does its contents match the users/passwords you are using? -- Carlos Mennens -- Adrian Klaver adrian.kla...@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
[GENERAL] dataset lock
Hello, I use a PG database on a HPC system (cluster). My processes get a dataset from the database and change the row, each process is independend. My table shows something like: id, status, data id = PK a unqiue number status a enum value which open, waiting, working, done So each process calls a SQL statement select * from where status = waiting, so the process should get the next waiting task, after the process gets the task, the status should be changed to working, so no other process shouldn't get the task. My processes are independed, so it can be, that 2 (or more) processes call the select statement at the same time and get in this case equal tasks, so I need some locking. How can I do this with Postgres, that each row / task in my table is read / write by one process. On threads I would create a mutex eg: lock() row = select * from table where status = waiting update status = working from table where id = row.id unlock() do something with row Which is the best solution with postgres? should I create a procedure which takes the next job, change it and returns the id, so each process calls select getNextJob() ? Thanks Phil -- 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] currval and DISCARD ALL
On 04/15/2013 05:57 PM, Adrian Klaver wrote: On 04/15/2013 02:42 PM, Nigel Heron wrote: Hi, is there a way to clear the session state of sequence values fetched by currval(regclass)? DISCARD ALL doesn't seem to do it. snip Might want to take a look at: http://www.depesz.com/2012/12/02/what-is-the-point-of-bouncing/ for some hints on dealing with sequences and pgBouncer. thanks, I read it (his blogs are always interesting!). I'm not disputing that calling currval() at the wrong time is a bad idea. I'm just wondering why DISCARD ALL clears everything but this? from the docs: DISCARD ALL resets a session to its original state, discarding temporary resources and resetting session-local configuration changes. .. but at the beginning of a session currval(foo) would return an error, whereas calling nexval(foo); DISCARD ALL; currval(foo); does not return an error.. clearly something isn't getting reset to the original state. If you create a TEMP sequence, then DISCARD ALL does clear the state, probably because the underlying table disappears. -nigel. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] configure --enable-dtrace ??systemtap?? on centos6.4 x86_32??/usr/bin/stap: invalid option -- 'C'
hi:Linux centos6.4 2.6.32-358.el6.i686 #1 SMP Thu Feb 21 21:50:49 UTC 2013 i686 i686 i386 GNU/Linux stap -v A script must be specified. Systemtap translator/driver (version 1.8/0.152 non-git sources) Copyright (C) 2005-2012 Red Hat, Inc. and others my configure: ./configure --enable-debug --enable-dtrace --prefix=/home/tom/pg9.2.1/ DTRACE=/usr/bin/stap --without-zlib error is: make[4]: Entering directory `/home/tom/postgresql-9.2.1/src/backend' make[4]: Nothing to be done for `submake-errcodes'. make[4]: Leaving directory `/home/tom/postgresql-9.2.1/src/backend' make[3]: Leaving directory `/home/tom/postgresql-9.2.1/src/port' make -C catalog schemapg.h make[3]: Entering directory `/home/tom/postgresql-9.2.1/src/backend/catalog' make[3]: `schemapg.h' is up to date. make[3]: Leaving directory `/home/tom/postgresql-9.2.1/src/backend/catalog' prereqdir=`cd 'parser/' /dev/null pwd` \ cd '../../src/include/parser/' rm -f gram.h \ ln -s $prereqdir/gram.h . prereqdir=`cd 'catalog/' /dev/null pwd` \ cd '../../src/include/catalog/' rm -f schemapg.h \ ln -s $prereqdir/schemapg.h . prereqdir=`cd 'utils/' /dev/null pwd` \ cd '../../src/include/utils/' rm -f fmgroids.h \ ln -s $prereqdir/fmgroids.h . make -C utils probes.h make[3]: Entering directory `/home/tom/postgresql-9.2.1/src/backend/utils' /usr/bin/stap -C -h -s probes.d -o probes.h.tmp /usr/bin/stap: invalid option -- 'C' make[3]: *** [probes.h] Error 1 make[3]: Leaving directory `/home/tom/postgresql-9.2.1/src/backend/utils' make[2]: *** [utils/probes.h] Error 2 make[2]: Leaving directory `/home/tom/postgresql-9.2.1/src/backend' make[1]: *** [all-backend-recurse] Error 2 make[1]: Leaving directory `/home/tom/postgresql-9.2.1/src' make: *** [all-src-recurse] Error 2 Any suggestion? thank you very much!
Re: [GENERAL] configure --enable-dtrace （systemtap） on centos6.4 x86_32，/usr/bin/stap: invalid option -- 'C'
wk wrote: hi:Linux centos6.4 2.6.32-358.el6.i686 #1 SMP Thu Feb 21 21:50:49 UTC 2013 i686 i686 i386 GNU/Linux stap -v A script must be specified. Systemtap translator/driver (version 1.8/0.152 non-git sources) Copyright (C) 2005-2012 Red Hat, Inc. and others my configure: ./configure --enable-debug --enable-dtrace --prefix=/home/tom/pg9.2.1/ DTRACE=/usr/bin/stap --without-zlib error is: make[3]: Entering directory `/home/tom/postgresql-9.2.1/src/backend/utils' /usr/bin/stap -C -h -s probes.d -o probes.h.tmp /usr/bin/stap: invalid option -- 'C' Evidently our DTrace support hasn't been ported to the systemtap emulation; or maybe you need some more config options. I think you will need to hack the makefiles until it works, and we could use a patch. I'm sure many will be happy to use SystemTap with our dtrace tracepoints. (I vaguely recall somebody saying this already worked, years ago; maybe you should troll the pgsql-hackers archives.) -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- 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] configure --enable-dtrace （systemtap） on centos6.4 x86_32，/usr/bin/stap: invalid option -- 'C'
Alvaro Herrera alvhe...@2ndquadrant.com writes: wk wrote: make[3]: Entering directory `/home/tom/postgresql-9.2.1/src/backend/utils' /usr/bin/stap -C -h -s probes.d -o probes.h.tmp /usr/bin/stap: invalid option -- 'C' Evidently our DTrace support hasn't been ported to the systemtap emulation; or maybe you need some more config options. --enable-dtrace has worked just fine in Fedora and RHEL builds for several years now. What version of systemtap are you using exactly? (A look into Red Hat's bugzilla suggests that versions later than somewhere around 0.9.9 ought to work.) 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] configure --enable-dtrace （systemtap） on centos6.4 x86_32，/usr/bin/stap: invalid option -- 'C'
I wrote: --enable-dtrace has worked just fine in Fedora and RHEL builds for several years now. What version of systemtap are you using exactly? Actually, reading it again, the problem is this: DTRACE=/usr/bin/stap which is 100% wrong; stap is not a substitute for dtrace. You should have a program named dtrace on your system. If you don't, you forgot to install systemtap-sdt-devel. 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] dataset lock
On Apr 16, 2013, at 7:50 AM, Philipp Kraus philipp.kr...@flashpixx.de wrote: Hello, I use a PG database on a HPC system (cluster). My processes get a dataset from the database and change the row, each process is independend. My table shows something like: id, status, data id = PK a unqiue number status a enum value which open, waiting, working, done So each process calls a SQL statement select * from where status = waiting, so the process should get the next waiting task, after the process gets the task, the status should be changed to working, so no other process shouldn't get the task. My processes are independed, so it can be, that 2 (or more) processes call the select statement at the same time and get in this case equal tasks, so I need some locking. How can I do this with Postgres, that each row / task in my table is read / write by one process. On threads I would create a mutex eg: lock() row = select * from table where status = waiting update status = working from table where id = row.id unlock() do something with row Which is the best solution with postgres? should I create a procedure which takes the next job, change it and returns the id, so each process calls select getNextJob() ? select for update might be the answer to what you're asking for - it'll lock the rows matched until the end of the transaction, blocking any other select for update on the same rows. If performance is important then you might want to look at some of the off the shelf queuing systems instead - PgQ or queue_classic, for instance. 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] dataset lock
On 2013-04-16 19:11:20 +0200, Steve Atkins said: On Apr 16, 2013, at 7:50 AM, Philipp Kraus philipp.kr...@flashpixx.de wrote: Hello, I use a PG database on a HPC system (cluster). My processes get a dataset from the database and change the row, each process is independend. My table shows something like: id, status, data id = PK a unqiue number status a enum value which open, waiting, working, done So each process calls a SQL statement select * from where status = waiting, so the process should get the next waiting task, after the process gets the task, the status should be changed to working, so no other process shouldn't get the task. My processes are independed, so it can be, that 2 (or more) processes call the select statement at the same time and get in this case equal tasks, so I need some locking. How can I do this with Postgres, that each row / task in my table is read / write by one process. On threads I would create a mutex eg: lock() row = select * from table where status = waiting update status = working from table where id = row.id unlock() do something with row Which is the best solution with postgres? should I create a procedure which takes the next job, change it and returns the id, so each process calls select getNextJob() ? select for update might be the answer to what you're asking for - it'll lock the rows matched until the end of the transaction, blocking any other select for update on the same rows. Okay my explaination are a little bit bad, so I try it in another way: My PG database is connected to differend cluster nodes (MPI). Each programm / process on each node are independed and run the SQL select * from table where status = waiting after that I update the row with the update statement (set status = working) so in this case one process can run the select, than comes another process and runs also the select, but both processes get an equal row. But this does not allowed. The second process need not see the row, which is taken by the first process. So can I suppress, that a select call sees a row, which is locked by a transaction? So I would like to do this with a store procedure, that runs the select and the update and after that it returns the PK of the selected dataset. If two (or more) processes run the SP at the same time, but the update can create an error, so the stored procedure is stopped and must called again. I need a solution, that a row, which is taken by one process not shown by all other processes Thx Phil -- 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 you spot the difference?
Hi PostgreSQL friends,
I have two databases in the same cluster that are almost identical. One is
a copy of the other as we are developing some new features in the copy.
My problem is that the exact same simple query performs great in the
original database (ises) and dismally in the copy database
(ises_coelacanth). The problem is that in ises, it uses an index scan,
but in ises_coelacanth it uses a sequential scan:
postgres@moshe=devmain:ises=# explain analyze SELECT count(*) FROM
tb_order_location ol JOIN tb_line_item li on li.order_location =
ol.order_location WHERE li.tracking_number = '10137378459';
QUERY PLAN
--
Aggregate (cost=671.48..671.49 rows=1 width=0) (actual
time=0.272..0.272 rows=1 loops=1)
- Nested Loop (cost=0.00..671.34 rows=54 width=0) (actual
time=0.124..0.265 rows=16 loops=1)
- Index Scan using tb_line_item_tracking_number_key on
tb_line_item li (cost=0.00..219.17 rows=54 width=4) (actual
time=0.087..0.161 rows=16 loops=1)
Index Cond: ((tracking_number)::text = '10137378459'::text)
- Index Scan using tb_order_location_pkey on tb_order_location
ol (cost=0.00..8.36 rows=1 width=4) (actual time=0.005..0.005 rows=1
loops=16)
Index Cond: (order_location = li.order_location)
Total runtime: 0.343 ms
(7 rows)
postgres@moshe=devmain:ises_coelacanth=# explain analyze SELECT count(*)
FROM tb_order_location ol JOIN tb_line_item li on li.order_location =
ol.order_location WHERE li.tracking_number = '10137378459';
QUERY PLAN
---
Aggregate (cost=50467.40..50467.41 rows=1 width=0) (actual
time=333.490..333.491 rows=1 loops=1)
- Hash Join (cost=26551.11..50447.62 rows=7915 width=0) (actual
time=332.045..333.481 rows=16 loops=1)
Hash Cond: (li.order_location = ol.order_location)
- Bitmap Heap Scan on tb_line_item li (cost=177.82..20715.03
rows=7915 width=4) (actual time=0.128..0.209 rows=16 loops=1)
Recheck Cond: ((tracking_number)::text =
'10137378459'::text)
- Bitmap Index Scan on tb_line_item_tracking_number_key
(cost=0.00..175.84 rows=7915 width=0) (actual time=0.108..0.108 rows=16
loops=1)
Index Cond: ((tracking_number)::text =
'10137378459'::text)
- Hash (cost=13190.24..13190.24 rows=803524 width=4) (actual
time=324.114..324.114 rows=803553 loops=1)
Buckets: 4096 Batches: 32 Memory Usage: 887kB
- Seq Scan on tb_order_location ol (cost=0.00..13190.24
rows=803524 width=4) (actual time=0.024..144.581 rows=803553 loops=1)
Total runtime: 333.766 ms
(11 rows)
Both of these queries return 16 rows, as you can see.
Below I've included the information on each of these tables. They have the
same indexes and are identical for the purposes of this query.
Can you help me figure out what is going on here?? Thank you!
postgres@moshe=devmain:ises=# \d tb_line_item
Table public.tb_line_item
Column |Type |
Modifiers
+-+---
line_item | integer | not null
default nextval('sq_pk_line_item'::regclass)
(...)
order_location | integer | not null
(...)
tracking_number| character varying(512) |
(...)
Indexes:
tb_line_item_pkey PRIMARY KEY, btree (line_item)
tb_line_item_order_catalog_article_key UNIQUE CONSTRAINT, btree
(order_catalog_article, order_location, project, creator)
tb_line_item_order_vendor_article_key UNIQUE CONSTRAINT, btree
(order_vendor_article, order_location, project, creator)
idx_line_item_canceled btree (canceled)
ix_line_item_project btree (project)
ix_line_item_reset btree (reset)
tb_line_item_order_location_key btree (order_location)
tb_line_item_tracking_number_key btree (tracking_number)
Check constraints:
chk_order_vendor_article_or_order_catalog_article CHECK
(order_vendor_article IS NULL AND order_catalog_article IS NOT NULL OR
order_vendor_article IS NOT NULL AND order_catalog_article IS NULL)
tb_line_item_check CHECK (
CASE
WHEN executed IS NOT NULL AND canceled IS NOT NULL THEN false
ELSE true
END)
tb_line_item_quantity_backordered_check CHECK (quantity_backordered
= 0::numeric)
tb_line_item_quantity_ordered_check CHECK (quantity_ordered
0::numeric)
tb_line_item_unit_price_check CHECK (unit_price = 0::numeric)
Foreign-key constraints:
(...)
Re: [GENERAL] currval and DISCARD ALL
On 04/16/2013 08:07 AM, Nigel Heron wrote: On 04/15/2013 05:57 PM, Adrian Klaver wrote: On 04/15/2013 02:42 PM, Nigel Heron wrote: Hi, is there a way to clear the session state of sequence values fetched by currval(regclass)? DISCARD ALL doesn't seem to do it. snip Might want to take a look at: http://www.depesz.com/2012/12/02/what-is-the-point-of-bouncing/ for some hints on dealing with sequences and pgBouncer. thanks, I read it (his blogs are always interesting!). I'm not disputing that calling currval() at the wrong time is a bad idea. I'm just wondering why DISCARD ALL clears everything but this? Well per the docs: http://www.postgresql.org/docs/9.2/interactive/sql-discard.html DISCARD ALL is equivalent to: SET SESSION AUTHORIZATION DEFAULT; RESET ALL; DEALLOCATE ALL; CLOSE ALL; UNLISTEN *; SELECT pg_advisory_unlock_all(); DISCARD PLANS; DISCARD TEMP; AFAIK, none of the above affect sequences. -nigel. -- Adrian Klaver adrian.kla...@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
[GENERAL] Postgresql default driver
Through a DNS, a C# program can access the Postgresql database either in Visual Studio or set it up as a website under localhost. But the Postgresql driver and the database name connection string works only if I run it in Visual Studio. As local host, it complains no default driver specified. I presume that either localhost or the internet user doesn't have access to the driver - but what could cause that? Mary Catelli
Re: [GENERAL] Can you spot the difference?
On 04/16/2013 12:07 PM, Moshe Jacobson wrote: Hi PostgreSQL friends, I have two databases in the same cluster that are almost identical. One is a copy of the other as we are developing some new features in the copy. My problem is that the exact same simple query performs great in the original database (ises) and dismally in the copy database (ises_coelacanth). The problem is that in ises, it uses an index scan, but in ises_coelacanth it uses a sequential scan: The difference is that Postgres is coming to alternate conclusions as to what plan to use. Given that the copy is causing the 'problem', the question to ask is; did you run ANALYZE on the table once the data was copied in? -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com mailto:mo...@neadwerx.com | www.neadwerx.com http://www.neadwerx.com/ Quality is not an act, it is a habit. -- Aristotle -- Adrian Klaver adrian.kla...@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] Can you spot the difference?
Moshe Jacobson mo...@neadwerx.com writes: My problem is that the exact same simple query performs great in the original database (ises) and dismally in the copy database (ises_coelacanth). The problem is that in ises, it uses an index scan, but in ises_coelacanth it uses a sequential scan: The rowcount estimates are much further away from reality in the second database. Either you forgot to run ANALYZE at all, or the stats target settings are different (and lower) in the second DB. 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 you spot the difference?
That was it! Thanks Adrian and Tom! On Tue, Apr 16, 2013 at 3:29 PM, Adrian Klaver adrian.kla...@gmail.comwrote: On 04/16/2013 12:07 PM, Moshe Jacobson wrote: Hi PostgreSQL friends, I have two databases in the same cluster that are almost identical. One is a copy of the other as we are developing some new features in the copy. My problem is that the exact same simple query performs great in the original database (ises) and dismally in the copy database (ises_coelacanth). The problem is that in ises, it uses an index scan, but in ises_coelacanth it uses a sequential scan: The difference is that Postgres is coming to alternate conclusions as to what plan to use. Given that the copy is causing the 'problem', the question to ask is; did you run ANALYZE on the table once the data was copied in? -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com mailto:mo...@neadwerx.com | www.neadwerx.com http://www.neadwerx.com/ Quality is not an act, it is a habit. -- Aristotle -- Adrian Klaver adrian.kla...@gmail.com -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] Can you spot the difference?
Statistics on your original and copy databases must be different.
For the same condition (li.tracking_number = '10137378459') optimizer expects
to find 7915 rows in tb_line_item table on the copy database while only 54
rows on the original database.
Also, the other table (tb_order_location) could have bad statistics as well.
That could cause different execution plans.
Regards,
Igor Neyman
From: Moshe Jacobson [mailto:mo...@neadwerx.com]
Sent: Tuesday, April 16, 2013 3:07 PM
To: pgsql-general
Subject: Can you spot the difference?
Hi PostgreSQL friends,
I have two databases in the same cluster that are almost identical. One is a
copy of the other as we are developing some new features in the copy.
My problem is that the exact same simple query performs great in the original
database (ises) and dismally in the copy database (ises_coelacanth). The
problem is that in ises, it uses an index scan, but in ises_coelacanth it uses
a sequential scan:
postgres@moshe=devmain:ises=# explain analyze SELECT count(*) FROM
tb_order_location ol JOIN tb_line_item li on li.order_location =
ol.order_location WHERE li.tracking_number = '10137378459';
QUERY PLAN
--
Aggregate (cost=671.48..671.49tel:671.48..671.49 rows=1 width=0) (actual
time=0.272..0.272 rows=1 loops=1)
- Nested Loop (cost=0.00..671.34 rows=54 width=0) (actual
time=0.124..0.265 rows=16 loops=1)
- Index Scan using tb_line_item_tracking_number_key on tb_line_item
li (cost=0.00..219.17 rows=54 width=4) (actual time=0.087..0.161 rows=16
loops=1)
Index Cond: ((tracking_number)::text = '10137378459'::text)
- Index Scan using tb_order_location_pkey on tb_order_location ol
(cost=0.00..8.36 rows=1 width=4) (actual time=0.005..0.005 rows=1 loops=16)
Index Cond: (order_location = li.order_location)
Total runtime: 0.343 ms
(7 rows)
postgres@moshe=devmain:ises_coelacanth=# explain analyze SELECT count(*) FROM
tb_order_location ol JOIN tb_line_item li on li.order_location =
ol.order_location WHERE li.tracking_number = '10137378459';
QUERY
PLAN
---
Aggregate (cost=50467.40..50467.41 rows=1 width=0) (actual
time=333.490..333.491 rows=1 loops=1)
- Hash Join (cost=26551.11..50447.62 rows=7915 width=0) (actual
time=332.045..333.481 rows=16 loops=1)
Hash Cond: (li.order_location = ol.order_location)
- Bitmap Heap Scan on tb_line_item li (cost=177.82..20715.03
rows=7915 width=4) (actual time=0.128..0.209 rows=16 loops=1)
Recheck Cond: ((tracking_number)::text = '10137378459'::text)
- Bitmap Index Scan on tb_line_item_tracking_number_key
(cost=0.00..175.84 rows=7915 width=0) (actual time=0.108..0.108 rows=16 loops=1)
Index Cond: ((tracking_number)::text = '10137378459'::text)
- Hash (cost=13190.24..13190.24 rows=803524 width=4) (actual
time=324.114..324.114 rows=803553 loops=1)
Buckets: 4096 Batches: 32 Memory Usage: 887kB
- Seq Scan on tb_order_location ol (cost=0.00..13190.24
rows=803524 width=4) (actual time=0.024..144.581 rows=803553 loops=1)
Total runtime: 333.766 ms
(11 rows)
Both of these queries return 16 rows, as you can see.
Below I've included the information on each of these tables. They have the same
indexes and are identical for the purposes of this query.
Can you help me figure out what is going on here?? Thank you!
postgres@moshe=devmain:ises=# \d tb_line_item
Table public.tb_line_item
Column |Type |
Modifiers
+-+---
line_item | integer | not null default
nextval('sq_pk_line_item'::regclass)
(...)
order_location | integer | not null
(...)
tracking_number| character varying(512) |
(...)
Indexes:
tb_line_item_pkey PRIMARY KEY, btree (line_item)
tb_line_item_order_catalog_article_key UNIQUE CONSTRAINT, btree
(order_catalog_article, order_location, project, creator)
tb_line_item_order_vendor_article_key UNIQUE CONSTRAINT, btree
(order_vendor_article, order_location, project, creator)
idx_line_item_canceled btree (canceled)
ix_line_item_project btree (project)
ix_line_item_reset btree (reset)
tb_line_item_order_location_key btree (order_location)
[GENERAL] JDBC driver versions
Hi all, I'm migrating from postgres 8.3 to 9.2. Included in my application is an old jar for the JDBC driver for the 8.3 database. Do I need a separate build with an up to date JDBC jar for 9.2 while I am testing out a new database or is it safe to use the latest JDBC jar with a very old version of postgres? Thanks, Ramsey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Mysterious table that exists but doesn't exist
Hi, I'm having a problem on a standby server (streaming replication) where a table seems to exist but is not queryable. Essentially a select statement (and drop/insert/etc) fails but \d and pg_tables show it exists. The table exists on the master (and is queryable) and replication is still working in that changes to the master table don't cause errors on the standby and changes to other tables are replicated and verified to be on the standby. Queries from the standby pasted below. I have a couple of questions that arise from this: 1) Any thoughts on what is going on here? 2) If there were corruption or something in the data for that particular table on the standby only, would replication report a failure (i.e., be unable to apply the binary changes) or would the binary changes still just sort of happen overtop the bad data? Because in this case, replication is still running without reporting any errors. 3) We managed to discover this by accident during some other routine work we do from a snapshot we'd taken of the standby drives (6 volume raid0). I had assumed that if replication and the pg_last_xlog_receive_location information was up to date then I was safe but, in this case, replication continued to run but the standby is essentially unusable as a failover option since this table is not available. Is there some other way to be certain that a standby server is consistent with master? Thanks, Dale psql session output live=# set search_path to someschema; SET live=# select * from tracked_deductibles; ERROR: relation tracked_deductibles does not exist LINE 1: select * from tracked_deductibles; live=# select * from someschema.tracked_deductibles; ERROR: relation someschema.tracked_deductibles does not exist LINE 1: select * from someschema.tracked_deductibles; live=# select *,'X'||tablename||'X' from pg_tables where schemaname = 'someschema' and tablename ilike '%tracked%'; schemaname |tablename| tableowner | tablespace | hasindexes | hasrules | hastriggers | ?column? +-++++--+-+--- someschema | tracked_deductibles | live || t | f| t | Xtracked_deductiblesX someschema | tracked_deductibles_log | live || t | f| f | Xtracked_deductibles_logX (2 rows) live=#\d List of relations Schema |Name | Type | Owner +-+--+--- snip someschema | tracked_deductibles | table| live someschema | tracked_deductibles_id_seq | sequence | live snip
Re: [GENERAL] JDBC driver versions
On 4/16/2013 1:13 PM, Ramsey Gurley wrote: I'm migrating from postgres 8.3 to 9.2. Included in my application is an old jar for the JDBC driver for the 8.3 database. Do I need a separate build with an up to date JDBC jar for 9.2 while I am testing out a new database or is it safe to use the latest JDBC jar with a very old version of postgres? latest version should work with older database servers.otherway around is less trustworthy (although it /probably/ will work for many purposes). -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] JDBC driver versions
On 04/16/2013 01:13 PM, Ramsey Gurley wrote: Hi all, I'm migrating from postgres 8.3 to 9.2. Included in my application is an old jar for the JDBC driver for the 8.3 database. Do I need a separate build with an up to date JDBC jar for 9.2 while I am testing out a new database or is it safe to use the latest JDBC jar with a very old version of postgres? From here: http://jdbc.postgresql.org/download.html#current Current Version This is the current version of the driver. Unless you have unusual requirements (running old applications or JVMs), this is the driver you should be using. It supports Postgresql 7.2 or newer and requires a 1.5 or newer JVM. It contains support for SSL and the javax.sql package. It comes in two flavours, JDBC3 and JDBC4. If you are using the 1.6 or 1.7 JVM, then you should use the JDBC4 version. Thanks, Ramsey -- Adrian Klaver adrian.kla...@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] Mysterious table that exists but doesn't exist
On 4/16/2013 1:29 PM, Dale Fukami wrote: live=# select * from someschema.tracked_deductibles; ERROR: relation someschema.tracked_deductibles does not exist LINE 1: select * from someschema.tracked_deductibles; live=#\d List of relations Schema |Name | Type | Owner +-+--+--- snip someschema | tracked_deductibles | table| live someschema any chance that has mixed case in it?try someschema.tracked_deductibles -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] Mysterious table that exists but doesn't exist
Tried the select with the quotes around each of the schema and table and I get the same result (someschema.tracked_deductibles). I'm pretty certain they're both lowercase. Would the select from pg_tables show if they were mixed case by accident? Dale On Tue, Apr 16, 2013 at 2:38 PM, John R Pierce pie...@hogranch.com wrote: On 4/16/2013 1:29 PM, Dale Fukami wrote: live=# select * from someschema.tracked_deductibles; ERROR: relation someschema.tracked_deductibles does not exist LINE 1: select * from someschema.tracked_deductibles; live=#\d List of relations Schema |Name | Type | Owner +-+--+--- snip someschema | tracked_deductibles | table| live someschema any chance that has mixed case in it?try someschema.tracked_deductibles -- john r pierce 37N 122W somewhere on the middle of the left coast
Re: [GENERAL] JDBC driver versions
Okay, I saw that, but I just wanted to double check. It seemed odd that there were builds going all the way back to 8.2 if the 9.2 version should work for everything. Thanks for the reassurance :-) Ramsey On Apr 16, 2013, at 1:36 PM, Adrian Klaver wrote: On 04/16/2013 01:13 PM, Ramsey Gurley wrote: Hi all, I'm migrating from postgres 8.3 to 9.2. Included in my application is an old jar for the JDBC driver for the 8.3 database. Do I need a separate build with an up to date JDBC jar for 9.2 while I am testing out a new database or is it safe to use the latest JDBC jar with a very old version of postgres? From here: http://jdbc.postgresql.org/download.html#current Current Version This is the current version of the driver. Unless you have unusual requirements (running old applications or JVMs), this is the driver you should be using. It supports Postgresql 7.2 or newer and requires a 1.5 or newer JVM. It contains support for SSL and the javax.sql package. It comes in two flavours, JDBC3 and JDBC4. If you are using the 1.6 or 1.7 JVM, then you should use the JDBC4 version. Thanks, Ramsey -- Adrian Klaver adrian.kla...@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] Can you spot the difference?
On Tue, Apr 16, 2013 at 3:29 PM, Adrian Klaver adrian.kla...@gmail.comwrote: Given that the copy is causing the 'problem', the question to ask is; did you run ANALYZE on the table once the data was copied in? I did not -- I expected the autovacuum daemon to do so. Why did it not? The database was created restored days ago, and the autovacuum daemon is running with default settings. Thanks. -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com | www.neadwerx.com Quality is not an act, it is a habit. -- Aristotle
Re: [GENERAL] Mysterious table that exists but doesn't exist
On 4/16/2013 1:48 PM, Dale Fukami wrote: Tried the select with the quotes around each of the schema and table and I get the same result (someschema.tracked_deductibles). I'm pretty certain they're both lowercase. Would the select from pg_tables show if they were mixed case by accident? yes, it would. I was assuming someschema was a made-up name to hide some business secrets. -- john r pierce 37N 122W somewhere on the middle of the left coast -- 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] Mysterious table that exists but doesn't exist
Dale Fukami dale.fuk...@gmail.com writes: I'm having a problem on a standby server (streaming replication) where a table seems to exist but is not queryable. Essentially a select statement (and drop/insert/etc) fails but \d and pg_tables show it exists. The table exists on the master (and is queryable) and replication is still working in that changes to the master table don't cause errors on the standby and changes to other tables are replicated and verified to be on the standby. That's peculiar. The most likely theories seem to be (1) corruption in the standby's pg_class indexes, such that an index search for relname = 'tracked_deductibles' fails to find the row, although it is found by seqscans; or (2) some type of visibility issue causing SnapshotNow operations to think the row is invalid, though it is valid to MVCC queries. Either way, if it's working on the master, then you've had a replication failure since the standby's files evidently don't match the master's. What PG version is this (and which versions have been installed since the replication was set up)? Have you had any system-level crashes on the standby? 2) If there were corruption or something in the data for that particular table on the standby only, would replication report a failure (i.e., be unable to apply the binary changes) or would the binary changes still just sort of happen overtop the bad data? Depends. It's not impossible that the corruption could magically disappear, if there's a full-page update sent from the master that overwrites whatever the problem is. Incremental updates on the same page would not make things better, of course, and could easily make them worse. It's unlikely that an incremental WAL replay operation would notice a problem that evidently isn't being noticed by regular queries. 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] SQL keywords are suddenly case sensitive
List, SQL seems to be behaving in a case-sensitive manner: universe=# select 1; ?column? -- 1 (1 row) universe=# SELECT 1; ERROR: syntax error at or near SELECT 1 LINE 1: SELECT 1; ^ I cannot figure out how this happened, and Google is not helping because all I'm getting is information about case-sensitivity in identifiers or in string comparison. I didn't even think this was possible, as I've always switched between lower case and upper case keywords, usually using lower case while testing and upper case to prettify scripts that I will be saving for reuse. version() = PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Regards, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu -- 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] JDBC driver versions
On 04/16/2013 01:54 PM, Ramsey Gurley wrote: Okay, I saw that, but I just wanted to double check. It seemed odd that there were builds going all the way back to 8.2 if the 9.2 version should work for everything. It works for Postgres going back to 7.2, but not for older JDK and JDBC versions. So per the docs they are kept around for those people that need to use them with older software. Thanks for the reassurance :-) Ramsey -- Adrian Klaver adrian.kla...@gmail.com -- Adrian Klaver adrian.kla...@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] Can you spot the difference?
On 04/16/2013 01:55 PM, Moshe Jacobson wrote: On Tue, Apr 16, 2013 at 3:29 PM, Adrian Klaver adrian.kla...@gmail.com mailto:adrian.kla...@gmail.com wrote: Given that the copy is causing the 'problem', the question to ask is; did you run ANALYZE on the table once the data was copied in? I did not -- I expected the autovacuum daemon to do so. Why did it not? The database was created restored days ago, and the autovacuum daemon is running with default settings. http://www.postgresql.org/docs/9.2/static/routine-vacuuming.html The autovacuum daemon, if enabled, will automatically issue ANALYZE commands whenever the content of a table has changed sufficiently. However, administrators might prefer to rely on manually-scheduled ANALYZE operations, particularly if it is known that update activity on a table will not affect the statistics of interesting columns. The daemon schedules ANALYZE strictly as a function of the number of rows inserted or updated; it has no knowledge of whether that will lead to meaningful statistical changes. So at a guess there has not been enough churn on the table. Thanks. -- Moshe Jacobson Nead Werx, Inc. | Manager of Systems Engineering 2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339 mo...@neadwerx.com mailto:mo...@neadwerx.com | www.neadwerx.com http://www.neadwerx.com/ Quality is not an act, it is a habit. -- Aristotle -- Adrian Klaver adrian.kla...@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] SQL keywords are suddenly case sensitive
On 04/16/2013 02:46 PM, Lee Hachadoorian wrote: List, SQL seems to be behaving in a case-sensitive manner: universe=# select 1; ?column? -- 1 (1 row) universe=# SELECT 1; ERROR: syntax error at or near SELECT 1 LINE 1: SELECT 1; ^ I cannot figure out how this happened, and Google is not helping because all I'm getting is information about case-sensitivity in identifiers or in string comparison. I didn't even think this was possible, as I've always switched between lower case and upper case keywords, usually using lower case while testing and upper case to prettify scripts that I will be saving for reuse. Interesting. Does this behavior survive logging out and then back into a session? Do you have any other client using the database that exhibits this behavior? Regards, --Lee -- Adrian Klaver adrian.kla...@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] SQL keywords are suddenly case sensitive
On 04/16/2013 02:46 PM, Lee Hachadoorian wrote: List, SQL seems to be behaving in a case-sensitive manner: universe=# select 1; ?column? -- 1 (1 row) universe=# SELECT 1; ERROR: syntax error at or near SELECT 1 LINE 1: SELECT 1; ^ I cannot figure out how this happened, and Google is not helping because all I'm getting is information about case-sensitivity in identifiers or in string comparison. I didn't even think this was possible, as I've always switched between lower case and upper case keywords, usually using lower case while testing and upper case to prettify scripts that I will be saving for reuse. Another question: Are the psql and Postgres versions the same? Regards, --Lee -- Adrian Klaver adrian.kla...@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] SQL keywords are suddenly case sensitive
On 04/16/2013 07:31 PM, Adrian Klaver wrote: On 04/16/2013 02:46 PM, Lee Hachadoorian wrote: List, SQL seems to be behaving in a case-sensitive manner: universe=# select 1; ?column? -- 1 (1 row) universe=# SELECT 1; ERROR: syntax error at or near SELECT 1 LINE 1: SELECT 1; ^ I cannot figure out how this happened, and Google is not helping because all I'm getting is information about case-sensitivity in identifiers or in string comparison. I didn't even think this was possible, as I've always switched between lower case and upper case keywords, usually using lower case while testing and upper case to prettify scripts that I will be saving for reuse. Interesting. Does this behavior survive logging out and then back into a session? It survives complete restart. (This is a laptop that I use for development and analysis, not a high-availability server, so the first thing I did when I realized my scripts started failing was reboot.) Do you have any other client using the database that exhibits this behavior? Same behavior in both psql and pgAdmin. Regards, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu -- 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] SQL keywords are suddenly case sensitive
On 04/16/2013 07:34 PM, Adrian Klaver wrote: On 04/16/2013 02:46 PM, Lee Hachadoorian wrote: List, SQL seems to be behaving in a case-sensitive manner: universe=# select 1; ?column? -- 1 (1 row) universe=# SELECT 1; ERROR: syntax error at or near SELECT 1 LINE 1: SELECT 1; ^ I cannot figure out how this happened, and Google is not helping because all I'm getting is information about case-sensitivity in identifiers or in string comparison. I didn't even think this was possible, as I've always switched between lower case and upper case keywords, usually using lower case while testing and upper case to prettify scripts that I will be saving for reuse. Another question: Are the psql and Postgres versions the same? Appears to both be 9.1.8. lee@tycho ~ $ psql -d universe psql (9.1.8) Type help for help. universe=# select version(); version -- PostgreSQL 9.1.8 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu/Linaro 4.6.3-1ubuntu5) 4.6.3, 64-bit Regards, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu -- 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] SQL keywords are suddenly case sensitive
On 04/16/2013 04:39 PM, Lee Hachadoorian wrote: On 04/16/2013 07:31 PM, Adrian Klaver wrote: On 04/16/2013 02:46 PM, Lee Hachadoorian wrote: Interesting. Does this behavior survive logging out and then back into a session? It survives complete restart. (This is a laptop that I use for development and analysis, not a high-availability server, so the first thing I did when I realized my scripts started failing was reboot.) Do you have any other client using the database that exhibits this behavior? Same behavior in both psql and pgAdmin. So when did this start? a) From creation of the database? b) At some point afterward? 3) If b), did something noteworthy to Postgres, an upgrade possibly? 4) Is there more than one version of Postgres on the machine? Regards, --Lee -- Adrian Klaver adrian.kla...@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] SQL keywords are suddenly case sensitive
Lee Hachadoorian lee.hachadooria...@gmail.com writes: SQL seems to be behaving in a case-sensitive manner: universe=# select 1; ?column? -- 1 (1 row) universe=# SELECT 1; ERROR: syntax error at or near SELECT 1 LINE 1: SELECT 1; ^ That's really bizarre, but I don't think it's a case sensitivity problem as such. Watch what I get from a syntax error on a normally-functioning system: $ psql psql (9.1.9) Type help for help. regression=# select 1; ?column? -- 1 (1 row) regression=# SELECT 1; ?column? -- 1 (1 row) regression=# xELECT 1; ERROR: syntax error at or near xELECT LINE 1: xELECT 1; ^ See the differences? The error message indicates that your parser saw SELECT 1 as all one token. Also, if you transcribed this accurately, it looks like the error cursor is pointing to the second character of the SELECT not the first (could you confirm that?). Which is even more bizarre. I'm not sure what's going on, but I think it's more likely to be something to do with whitespace not being really whitespace than it is with case as such. Consider the possibility that you're somehow typing a non-breaking space or some such character. One thing that might be useful is to examine the error report in the postmaster log using an editor that will show you any non-printing characters. 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] SQL keywords are suddenly case sensitive
On 04/16/2013 08:23 PM, Tom Lane wrote: SELECT 1 as all one token. Also, if you transcribed this accurately, it looks like the error cursor is pointing to the second character of the SELECT not the first (could you confirm that?). Which is even more bizarre. No, that must have been an email formatting thing. In psql, the caret is under the S. Looking at the other issues you raised, but just wanted to provide a quick answer to that. Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu -- 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] SQL keywords are suddenly case sensitive
On Tue, Apr 16, 2013 at 08:57:02PM -0400, Lee Hachadoorian wrote: On 04/16/2013 08:23 PM, Tom Lane wrote: SELECT 1 as all one token. Also, if you transcribed this accurately, it looks like the error cursor is pointing to the second character of the SELECT not the first (could you confirm that?). Which is even more bizarre. No, that must have been an email formatting thing. In psql, the caret is under the S. Looking at the other issues you raised, but just wanted to provide a quick answer to that. Use SET log_statment='all' and look in the server logs for the query. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] SQL keywords are suddenly case sensitive
On 04/16/2013 08:23 PM, Tom Lane wrote: Lee Hachadoorian lee.hachadooria...@gmail.com writes: SQL seems to be behaving in a case-sensitive manner: universe=# select 1; ?column? -- 1 (1 row) universe=# SELECT 1; ERROR: syntax error at or near SELECT 1 LINE 1: SELECT 1; ^ That's really bizarre, but I don't think it's a case sensitivity problem as such. Watch what I get from a syntax error on a normally-functioning system: $ psql psql (9.1.9) Type help for help. regression=# select 1; ?column? -- 1 (1 row) regression=# SELECT 1; ?column? -- 1 (1 row) regression=# xELECT 1; ERROR: syntax error at or near xELECT LINE 1: xELECT 1; ^ See the differences? The error message indicates that your parser saw SELECT 1 as all one token. Also, if you transcribed this accurately, it looks like the error cursor is pointing to the second character of the SELECT not the first (could you confirm that?). Which is even more bizarre. I'm not sure what's going on, but I think it's more likely to be something to do with whitespace not being really whitespace than it is with case as such. Consider the possibility that you're somehow typing a non-breaking space or some such character. One thing that might be useful is to examine the error report in the postmaster log using an editor that will show you any non-printing characters. regards, tom lane Tom, Yes, nonbreaking spaces was the problem. Sorry for the red herring re: case sensitivity, and thanks for figuring it out. Adrian, thanks for your input as well. Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu -- 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] SQL keywords are suddenly case sensitive
I've seen this in the ticketing system Front Range where it sticks in nbsp into the text windows and if you copy and paste it won't work. Frustrating as all hell. On Tue, Apr 16, 2013 at 7:18 PM, Lee Hachadoorian lee.hachadooria...@gmail.com wrote: On 04/16/2013 08:23 PM, Tom Lane wrote: Lee Hachadoorian lee.hachadooria...@gmail.com writes: SQL seems to be behaving in a case-sensitive manner: universe=# select 1; ?column? -- 1 (1 row) universe=# SELECT 1; ERROR: syntax error at or near SELECT 1 LINE 1: SELECT 1; ^ That's really bizarre, but I don't think it's a case sensitivity problem as such. Watch what I get from a syntax error on a normally-functioning system: $ psql psql (9.1.9) Type help for help. regression=# select 1; ?column? -- 1 (1 row) regression=# SELECT 1; ?column? -- 1 (1 row) regression=# xELECT 1; ERROR: syntax error at or near xELECT LINE 1: xELECT 1; ^ See the differences? The error message indicates that your parser saw SELECT 1 as all one token. Also, if you transcribed this accurately, it looks like the error cursor is pointing to the second character of the SELECT not the first (could you confirm that?). Which is even more bizarre. I'm not sure what's going on, but I think it's more likely to be something to do with whitespace not being really whitespace than it is with case as such. Consider the possibility that you're somehow typing a non-breaking space or some such character. One thing that might be useful is to examine the error report in the postmaster log using an editor that will show you any non-printing characters. regards, tom lane Tom, Yes, nonbreaking spaces was the problem. Sorry for the red herring re: case sensitivity, and thanks for figuring it out. Adrian, thanks for your input as well. Best, --Lee -- Lee Hachadoorian Assistant Professor in Geography, Dartmouth College http://freecity.commons.gc.cuny.edu -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to build my own 9.2.4 installer package for IBM Power System ppc64
Hi, I have managed to install 9.2.4 to an IBM Power System server ppc64 by compiling pg 9.2.4 from source as I cannot find the 9.2.4 installer package for ppc64. Can anyone advise me how to build my own installer package for ppc64 (e.g. tools, steps)? since I have more than one ppc64 server and I want to avoid the compile-from-source approach. regards -- 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] How to build my own 9.2.4 installer package for IBM Power System ppc64
On 04/16/2013 07:04 PM, ascot.m...@gmail.com wrote: Hi, I have managed to install 9.2.4 to an IBM Power System server ppc64 by compiling pg 9.2.4 from source as I cannot find the 9.2.4 installer package for ppc64. Can anyone advise me how to build my own installer package for ppc64 (e.g. tools, steps)? since I have more than one ppc64 server and I want to avoid the compile-from-source approach. regards Are you using AIX or Linux? Either way... you can just use the same binaries/libs on each system (assuming they are all the same OS). Just tarball them up. JD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Most efficient way to insert without duplicates
Hi all! I track Twitter followers in my database. I have the following table: # \d persona_followers Table public.persona_followers Column|Type | Modifiers -+-+--- service_id | bigint | not null follower_id | bigint | not null valid_at| timestamp without time zone | Indexes: persona_followers_pkey PRIMARY KEY, btree (service_id, follower_id) The table IS NOT partitioned. I have a list of Twitter people I follow more - brands, actors, those kinds of Twitter accounts. They often have thousands, if not hundreds of thousands, of followers. I fetch the followers of these accounts about once a day. When it's time to insert into the database, I use the following algorithm: CREATE TEMP TABLE import( service_id bigint, follower_id bigint ); COPY INTO import FROM STDIN; ... \N INSERT INTO persona_followers(service_id, follower_id, valid_at) SELECT service_id, follower_id, NOW() FROM (SELECT DISTINCT service_id, follower_id FROM import) AS import WHERE NOT EXISTS(SELECT * FROM persona_followers WHERE import.service_id = persona_followers.service_id AND import.follower_id = persona_followers.follower_id); I currently have 660 million rows in persona_followers (47 GB). A test import is 13.5 million rows (571 MB). The real daily import will be at least 10x more. In a 24 hour period, I will have at most a few thousand *new* rows - the rest will already exist in persona_followers. How do I most efficiently eliminate the duplicates? Should I delete the duplicates in import? Or should I bite the bullet and EXCEPT the final table? Should I insert much smaller batches? Or is the above already the most efficient way? What other completely different data structure could I use to achieve my goal? I truly need the exhaustive list of followers because we do reach calculations (number of unique accounts which received a particular tweet). The true answer is probably benchmark on your own servers, but I'm looking for guidelines, people with the same kind of experience. Thanks! François smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] How to build my own 9.2.4 installer package for IBM Power System ppc64
On 4/16/2013 7:04 PM, ascot.m...@gmail.com wrote: I have managed to install 9.2.4 to an IBM Power System server ppc64 by compiling pg 9.2.4 from source as I cannot find the 9.2.4 installer package for ppc64. Can anyone advise me how to build my own installer package for ppc64 (e.g. tools, steps)? since I have more than one ppc64 server and I want to avoid the compile-from-source approach. on AIX, I build mine to run in /opt/$MYGROUP/pgsql/9.2 and I just tar it up and distribute it as a tarball to my operations people. I'd previously asked them if they wanted it as a system package, and their response was 'why bother?'. I throw in a script that adds it as a service (via /etc/init.d rc3.d, I'm too lazy to figure out the new AIX 6.1 service manager). -- john r pierce 37N 122W somewhere on the middle of the left coast -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
