[GENERAL] update with from
Postgresql 8.2 I want to update a table with a from that has mutliple rows joining to it. According to the docs, this is not advisable because: If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable. In my tests, if the joined rows are sorted it always updates with the first row. Does anyone have any other experiences, or should I be concerned that at some point it will behave differently? The performance is currently unacceptable when changing the from to only join to one row makes Thanks Sim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] xml, xpath,postgres 9.1
Hello everybody, sorry for my english.
I need to extract the xml: lat, lon and ele. The xml I have it save on a
table (gpx.object_value). For the first two works for me:
SELECT (xpath ('//lat ', gpx.object_value)) [i] FROM gpx
SELECT (xpath ('//lon ', gpx.object_value)) [i] FROM gpx
My question is how do ele ? Probe of everything:
'./trk/trkseg/trkpt/ele/text()'
'//ele'
'//ele/text()'
'//*[//ele]'
Please,help me.
TKANKS
Information:
postgres(postgis),eclipse,jsp
INSERT INTO gpx(object_name, object_value) VALUES ('t1.gpx',
cast(pg_read_file('t1.gpx', 0, 100) As xml));
Document xml:
- gpx xmlns=http://www.topografix.com/GPX/1/1; creator=MapMyTracks
version=1.1 xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance;
xsi:schemaLocation=http://www.topografix.com/GPX/1/1
http://www.topografix.com/GPX/1/1/gpx.xsd;
- trk
- trkseg
- trkpt lat=12.645648333 lon=-7.884185
ele20.2/ele
time2007-12-30T08:27:03Z/time
/trkpt
- trkpt lat ...
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/xml-xpath-postgres-9-1-tp5164387p5164387.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
Re: [GENERAL] Best way to create unique primary keys across schemas?
On Mon, Jan 23, 2012 at 11:19 AM, panam pa...@gmx.net wrote:
Hi,
If I'd like to have primary keys generated (numeric style, no UUIDs) that
are unique across schemas is the best option to allocate a fixed sequence
range (min,max) to the sequences of all schemas?
You can share a sequence object between several tables. This can
happen somewhat unexpectedly, as I found out to my surprise a while
ago:
CREATE TABLE tbl1 (ID serial primary key,foo varchar,bar varchar);
INSERT INTO tbl1 (foo,bar) VALUES ('asdf','qwer');
CREATE TABLE tbl2 LIKE tbl1 INCLUDING ALL;
INSERT INTO tbl2 (foo,bar) VALUES ('hello','world');
Both tables will be drawing IDs from the same sequence object, because
create table like copies the default value, not the serial
shorthand. (It makes perfect sense, it just surprised me that the IDs
were looking a little odd.)
Chris Angelico
--
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] PGbouncer for Windows 2008
Hi. John-san, thanks! to Edison-san. It is necessary to perform it in an administrator's mode. Please see, http://winpg.jp/~saito/pgbouncer/try_64bit/regsvr32_pgevent.png http://winpg.jp/~saito/pgbouncer/try_64bit/running_pgbouncer.png Regards, Hiroshi Saito (2012/01/23 10:09), John R Pierce wrote: On 01/22/12 4:55 PM, Edison So wrote: I downloaded the 64-bit version and extracted it. Unfortunately. it seems that the pbbouncer.exe file is needed to be rebulit (ie. recompiled) according to the following statements from the README file: those statements you reference may well be from the generic pgbouncer source readme. if someone has already built and tested a 64bit binary, whats the problem? -- 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] update with from
In my tests, if the joined rows are sorted it always updates with the first row. Does anyone have any other experiences, or should I be concerned that at some point it will behave differently? I checked my tests again. It always uses the last one, not the first one. Sim -- 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] Is Synchronous Postgresql Replication Slower Than Asynchronous?
On Sat, Jan 21, 2012 at 3:31 AM, Jerry Richards jerry.richa...@teotech.com wrote: Is synchronous postgresql replication slower than asynchronous? If so, how much? I am looking into database replication for a phone system, so the response time is of concern. You might want to investigate pgpool-ii. It sits as a proxy between the client and the databases, and as queries are executed simultaneously, a synchronous replication setup should be just as fast as an unreplicated setup. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ -- 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] update with from
On 23 January 2012 14:48, Sim Zacks s...@compulab.co.il wrote: In my tests, if the joined rows are sorted it always updates with the first row. Does anyone have any other experiences, or should I be concerned that at some point it will behave differently? I checked my tests again. It always uses the last one, not the first one. Sim I expect that your records get updated multiple times too. Perhaps you can use DISTINCT ON in your from-based UPDATE? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. -- 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] Is Synchronous Postgresql Replication Slower Than Asynchronous?
On Mon, Jan 23, 2012 at 2:30 PM, Stuart Bishop stu...@stuartbishop.net wrote: On Sat, Jan 21, 2012 at 3:31 AM, Jerry Richards jerry.richa...@teotech.com wrote: Is synchronous postgresql replication slower than asynchronous? If so, how much? I am looking into database replication for a phone system, so the response time is of concern. You might want to investigate pgpool-ii. It sits as a proxy between the client and the databases, and as queries are executed simultaneously, a synchronous replication setup should be just as fast as an unreplicated setup. Can you share your actual results on that? -- Simon Riggs http://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] could not accept SSPI security context
Ahmed wrote Hi, In my email, I mistakenly assumed that the next version would be 2.0.12.0, which was not. My bad. I checked the source and confirmed that the 2.0.11.0 has the bug, and the immediate next version (2.0.11.91) has the fix. You can use the version 2.0.11.92 lt;http://pgfoundry.org/frs/?group_id=1000140amp;release_id=1889gt;, which is the latest stable release. -Ahmed *@Ahmed -* Thank you so much. I just ran a quick test and everything looks like it's going to work great. I was really going to have to start pulling my hair out over that one if it didn't work. -- View this message in context: http://postgresql.1045698.n5.nabble.com/could-not-accept-SSPI-security-context-tp3275102p5166312.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
Re: [GENERAL] Is Synchronous Postgresql Replication Slower Than Asynchronous?
On Mon, Jan 23, 2012 at 9:37 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Jan 23, 2012 at 2:30 PM, Stuart Bishop stu...@stuartbishop.net wrote: On Sat, Jan 21, 2012 at 3:31 AM, Jerry Richards jerry.richa...@teotech.com wrote: Is synchronous postgresql replication slower than asynchronous? If so, how much? I am looking into database replication for a phone system, so the response time is of concern. You might want to investigate pgpool-ii. It sits as a proxy between the client and the databases, and as queries are executed simultaneously, a synchronous replication setup should be just as fast as an unreplicated setup. Can you share your actual results on that? No. This is based on my assumptions from the design, not from actual tests. I'm currently asynchronously replicated with Slony-I and looking at PG 9.1 builtin replication for our simpler clusters. -- Stuart Bishop stu...@stuartbishop.net http://www.stuartbishop.net/ -- 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] update with from
On Monday, January 23, 2012 2:00:29 am Sim Zacks wrote: Postgresql 8.2 I want to update a table with a from that has mutliple rows joining to it. According to the docs, this is not advisable because: If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable. In my tests, if the joined rows are sorted it always updates with the first row. Does anyone have any other experiences, or should I be concerned that at some point it will behave differently? The performance is currently unacceptable when changing the from to only join to one row makes I guess the primary question here is, what are you trying to achieve? Do want a particular row to supply the values to the target table i.e the row with the most timestamp? What is the query you are using? Thanks Sim -- 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] update with from
On 01/23/2012 04:34 PM, Alban Hertroys wrote: On 23 January 2012 14:48, Sim Zacks s...@compulab.co.il wrote: In my tests, if the joined rows are sorted it always updates with the first row. Does anyone have any other experiences, or should I be concerned that at some point it will behave differently? I checked my tests again. It always uses the last one, not the first one. Sim I expect that your records get updated multiple times too. Perhaps you can use DISTINCT ON in your from-based UPDATE? The select in the from statement (i.e. update.. from (select...) ) returns 2739 records. It updates 617 records. When I run the update it returns 617 rows affected So I'm guessing it is not multiple updates. Sim -- 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] Is Synchronous Postgresql Replication Slower Than Asynchronous?
On Mon, Jan 23, 2012 at 3:13 PM, Stuart Bishop stu...@stuartbishop.net wrote: On Mon, Jan 23, 2012 at 9:37 PM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Jan 23, 2012 at 2:30 PM, Stuart Bishop stu...@stuartbishop.net wrote: On Sat, Jan 21, 2012 at 3:31 AM, Jerry Richards jerry.richa...@teotech.com wrote: Is synchronous postgresql replication slower than asynchronous? If so, how much? I am looking into database replication for a phone system, so the response time is of concern. You might want to investigate pgpool-ii. It sits as a proxy between the client and the databases, and as queries are executed simultaneously, a synchronous replication setup should be just as fast as an unreplicated setup. Can you share your actual results on that? No. This is based on my assumptions from the design, not from actual tests. I'm currently asynchronously replicated with Slony-I and looking at PG 9.1 builtin replication for our simpler clusters. Sync rep 9.1 allows you to have 2 servers involved, which is really necessary for availability and robustness. -- Simon Riggs http://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] update with from
On 01/23/2012 05:13 PM, Adrian Klaver wrote: I guess the primary question here is, what are you trying to achieve? Do want a particular row to supply the values to the target table i.e the row with the most timestamp? What is the query you are using? The query returns a partid, unitprice and delivery weeks from the latest set of rfqs sent. I want to update the table with the delivery weeks per part of the cheapest of those rfqs. This is the update stmt I am using, assuming that it always updates the table with the last row per part: update stat_allocated_components a set partarrivedate=current_date+(b.deliverywks*7),partarrivedate_source='RFQ Est' from (select b.popartid,b.partid,b.unitprice,b.deliverywks from poparts b join pos c using(poid) join lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate d.issuedate-7 AND b.unitprice 0::numeric AND b.quantity = 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) order by b.partid,b.unitprice desc, b.deliverywks desc) b where a.partid=b.partid and partarrivedate is null and a.stock-a.previouscommitmentlf+a.quantity0 and b.deliverywks is not null This query take 163 ms. When I throw in code to make the select only return the correct rows The select statement takes 9 secs by itself: select a.partid,a.deliverywks from poparts a where popartid in ( select b.popartid from poparts b join pos c using(poid) join stock.lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate d.issuedate-7 AND b.unitprice 0::numeric AND b.quantity = 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) and b.partid=a.partid order by b.partid,b.unitprice, b.deliverywks limit 1 ) -- 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] update with from
Sim Zacks s...@compulab.co.il writes: I want to update a table with a from that has mutliple rows joining to it. According to the docs, this is not advisable because: If it does, then only one of the join rows will be used to update the target row, but which one will be used is not readily predictable. That means exactly what it says. In my tests, if the joined rows are sorted it always updates with the first row. Does anyone have any other experiences, or should I be concerned that at some point it will behave differently? If you rely on this, your code *will* break some day. Probably at 3AM while you're on vacation. All it takes is a plan change. 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] [RFE] auto ORDER BY for SELECT
I'm not sure if this is the right list to discuss this, but, I have a suggestion: ORDER BY clause, as defined in the SELECT documentation says: If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce This order is usually not wanted, as it is not predictable. I believe many people would expect the order of rowsreturned in this case, to be ordered as the primary key of the table, or the same order the rows were inserted. I suggest to change this behavior. If one makes a SELECT statement without any ORDER BY, it would beclever to automatically sort by the first primary key found in the query, if any.The present behavior would still be used in case of queries without any primary key fields. This would save a lot of repeated clauses ORDER BY table primary key that we have to add to every SELECT, even the most simple oneSELECT * FROM tableIf we actually want the order of the rows to make any sense.
Re: [GENERAL] Schema version control
On Thu, Feb 10, 2011 at 02:58:15PM -0700, Rob Sargent wrote: On 02/10/2011 02:38 PM, Royce Ausburn wrote: My company is having trouble managing how we upgrade schema changes across many versions of our software. I imagine this is a common problem and there're probably some neat solutions that we don't know about. For the last 10 years we have been writing bash shell scripts essentially numbered in order db0001, db0002, db0003 The number represents the schema version which is recorded in the database and updated by the shell scripts. We have a template that provides all the functionality we need, we just copy the script and fill in the blanks. The schema upgrade scripts are committed to svn along with the software changes, and we have a process when installing the software at a site that runs the scripts on the DB in order before starting up the new version of the software. Don't the bash scripts get checked in to .../perforce/cvs/svn/git/...? Aren't they part of the resources of the project(s)? I was thinking about this a little more. With the new CREATE EXTENSION functionality in Postgres, we have the infrastructure to run various SQL scripts to migrate between versioned states. Obviously the extension code relates to extensions such as datatypes. I was wondering if this is sufficiently generic that it could be used to migrate between different versions of a schema? This wouldn't be using the EXTENSION functionality, just the ability to run the scripts. This would enable easy upgrades (and downgrades, branching etc.) between different schema versions, providing that the appropriate scripts were installed. If this were optionally also accessible via an SQL syntax such as an analogue of CREATE and/or ALTER EXTENSION, it would provide a reliable and standardised method for installing and upgrading a schema, which would potentially prevent a great deal of wheel-reinvention between software packages. Regards, Roger -- .''`. Roger Leigh : :' : Debian GNU/Linux http://people.debian.org/~rleigh/ `. `' Printing on GNU/Linux? http://gutenprint.sourceforge.net/ `-GPG Public Key: 0x25BFB848 Please GPG sign your mail. -- 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] Schema version control
In response to Roger Leigh rle...@codelibre.net: On Thu, Feb 10, 2011 at 02:58:15PM -0700, Rob Sargent wrote: On 02/10/2011 02:38 PM, Royce Ausburn wrote: My company is having trouble managing how we upgrade schema changes across many versions of our software. I imagine this is a common problem and there're probably some neat solutions that we don't know about. http://dbsteward.org We've been using this for several years and it's made our schema versioning almost a non-issue. Finally got the go-ahead to release it just this month. -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] [RFE] auto ORDER BY for SELECT
Hello 2012/1/23 Douglas Eric sekk...@hotmail.com: I'm not sure if this is the right list to discuss this, but, I have a suggestion: ORDER BY clause, as defined in the SELECT documentation says: If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce This order is usually not wanted, as it is not predictable. I believe many people would expect the order of rows returned in this case, to be ordered as the primary key of the table, or the same order the rows were inserted. I suggest to change this behavior. If one makes a SELECT statement without any ORDER BY, it would be clever to automatically sort by the first primary key found in the query, if any. The present behavior would still be used in case of queries without any primary key fields. This would save a lot of repeated clauses ORDER BY table primary key that we have to add to every SELECT, even the most simple one SELECT * FROM table If we actually want the order of the rows to make any sense. * I don't think so this is good idea. Any sort is not cheap - so ORDER BY hint - yes, user can do expensive operation. * second argument SELECT * FROM longtab LIMIT 100 is significantly faster than SELECT * FROM longtab ORDER BY PK LIMIT 100; so implicit ORDER BY can significantly increase a load of server Regards Pavel Stehule -- 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] [RFE] auto ORDER BY for SELECT
Douglas Eric sekk...@hotmail.com wrote: I'm not sure if this is the right list to discuss this, but, I have a suggestion: ORDER BY clause, as defined in the SELECT documentation says: If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce This order is usually not wanted, as it is not predictable. I believe many people would expect the order of rows returned in this case, to be ordered as the primary key of the table, or the same order the rows were inserted. I suggest to change this behavior. If one makes a SELECT statement without any ORDER BY, it would be clever to automatically sort by the first primary key found in the query, if any. No. Since 8.3 (IIRC) we have a feature called 'concurrent seq. scan', see: http://j-davis.com/postgresql/83v82_scans.html Your suggestion can't work in this way. That's only one problem, there are more. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly. (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] [RFE] auto ORDER BY for SELECT
Piling On. NO! (not that I actually think this would ever happen anyway). Sorting is a resource-intensive process and it should NOT be made into a default. Besides, any kind of intelligent/implicit behavior like that ends up being forgotten and/or changed in the future and previously working queries begin to break. SQL is an explicit, declarative, language and thus will tend toward having verbose syntax generally - this is a good thing since while you are in the middle of writing a query you may be frustrated above having to explain every little thing but when you come back to the code a year from now you have a good chance of knowing exactly what it is doing AND when you upgrade PostgreSQL you have less exposure to the breakage of existing queries since the server is not making guesses as to what you want/mean. David J. From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Douglas Eric Sent: Monday, January 23, 2012 7:18 AM To: pgsql-general@postgresql.org Subject: [GENERAL] [RFE] auto ORDER BY for SELECT I'm not sure if this is the right list to discuss this, but, I have a suggestion: ORDER BY clause, as defined in the SELECT documentation http://www.postgresql.org/docs/9.1/static/sql-select.html says: If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce This order is usually not wanted, as it is not predictable. I believe many people would expect the order of rows returned in this case, to be ordered as the primary key of the table, or the same order the rows were inserted. I suggest to change this behavior. If one makes a SELECT statement without any ORDER BY, it would be clever to automatically sort by the first primary key found in the query, if any. The present behavior would still be used in case of queries without any primary key fields. This would save a lot of repeated clauses ORDER BY table primary key that we have to add to every SELECT, even the most simple one SELECT * FROM table If we actually want the order of the rows to make any sense.
Re: [GENERAL] update with from
On Monday, January 23, 2012 7:32:35 am Sim Zacks wrote: On 01/23/2012 05:13 PM, Adrian Klaver wrote: I guess the primary question here is, what are you trying to achieve? Do want a particular row to supply the values to the target table i.e the row with the most timestamp? What is the query you are using? The query returns a partid, unitprice and delivery weeks from the latest set of rfqs sent. I want to update the table with the delivery weeks per part of the cheapest of those rfqs. This is the update stmt I am using, assuming that it always updates the table with the last row per part: update stat_allocated_components a set partarrivedate=current_date+(b.deliverywks*7),partarrivedate_source='RFQ Est' from (select b.popartid,b.partid,b.unitprice,b.deliverywks from poparts b join pos c using(poid) join lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate d.issuedate-7 AND b.unitprice 0::numeric AND b.quantity = 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) order by b.partid,b.unitprice desc, b.deliverywks desc) b where a.partid=b.partid and partarrivedate is null and a.stock-a.previouscommitmentlf+a.quantity0 and b.deliverywks is not null This query take 163 ms. When I throw in code to make the select only return the correct rows The select statement takes 9 secs by itself: select a.partid,a.deliverywks from poparts a where popartid in ( select b.popartid from poparts b join pos c using(poid) join stock.lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate d.issuedate-7 AND b.unitprice 0::numeric AND b.quantity = 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) and b.partid=a.partid order by b.partid,b.unitprice, b.deliverywks limit 1 ) From what I can see they are not the same queries, notwithstanding the selectivity in the second query. In fact I am not sure what the second query accomplishes that cannot be done in the first query:) Would you not get the same result in the first query by doing something like: select b.popartid,b.partid,b.unitprice,b.deliverywks from poparts b join pos c using(poid) join lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate d.issuedate-7 AND b.unitprice 0::numeric AND b.quantity = 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) order by b.partid,b.unitprice desc, b.deliverywks desc limit 1 -- 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] update with from
On Monday, January 23, 2012 7:32:35 am Sim Zacks wrote: On 01/23/2012 05:13 PM, Adrian Klaver wrote: When I throw in code to make the select only return the correct rows The select statement takes 9 secs by itself: select a.partid,a.deliverywks from poparts a where popartid in ( select b.popartid from poparts b join pos c using(poid) join stock.lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate d.issuedate-7 AND b.unitprice 0::numeric AND b.quantity = 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) and b.partid=a.partid order by b.partid,b.unitprice, b.deliverywks limit 1 ) To clarify what I posted earlier, my suggestion was based on rewriting the second query as: select b.partid,b.deliverywks b.popartid from poparts b join pos c using(poid) join stock.lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate d.issuedate-7 AND b.unitprice 0::numeric AND b.quantity = 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) order by b.partid,b.unitprice, b.deliverywks limit 1 I may be missing the intent of your original query, but I think the above gets to the same result without the IN. -- 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] Question about (probably wrong) index scan cost for conditional indexes
Hi.
Seems previous test case not clear demonstrate the problem which i have
stuck with.
Now much better and close to reality test case:
Preparation:
set random_page_cost to 4;
set seq_page_cost to 1;
create table test (id integer primary key, sections integer[], value float);
insert into test select id, ('{'||((random()*10)::integer)||'}')::integer[]
as value, random() as value from generate_series(1,100) as g(id);
--generic gist index for array
CREATE INDEX test_sections_gist on test using gist(sections);
--specialized index on value for sections '{2}'
CREATE INDEX test_value_in2section_key on test(value) where sections
'{2}';
analyze test;
Now actual tests:
Good query but cost definitely wrong:
postgres=# EXPLAIN ANALYZE SELECT * from test where sections '{2}' order
by value limit 100;
QUERY PLAN
---
Limit (cost=0.00..539.29 rows=100 width=37) (actual time=0.043..0.499
rows=100 loops=1)
- Index Scan using test_value_in2section_key on test
(cost=0.00..5392.87 rows=1000 width=37) (actual time=0.040..0.434 rows=100
loops=1)
Total runtime: 0.570 ms
Compare with almost equivalent query:
postgres=# EXPLAIN ANALYZE SELECT * from test order by id limit 100;
QUERY PLAN
---
Limit (cost=0.00..3.43 rows=100 width=37) (actual time=0.057..0.192
rows=100 loops=1)
- Index Scan using test_pkey on test (cost=0.00..34317.36
rows=100 width=37) (actual time=0.054..0.115 rows=100 loops=1)
Total runtime: 0.258 ms
Actual speed almost same but cost differs 100 times.
Now if I increase the limit I start getting slow plans because it switch to
GIST index and bitmap scan (because cost of common index scan too high):
postgres=# EXPLAIN ANALYZE SELECT * from test where sections '{2}' order
by value limit 1000;
QUERY
PLAN
-
Limit (cost=2941.68..2944.18 rows=1000 width=37) (actual
time=175.301..175.766 rows=1000 loops=1)
- Sort (cost=2941.68..2944.18 rows=1000 width=37) (actual
time=175.298..175.541 rows=1000 loops=1)
Sort Key: value
Sort Method: top-N heapsort Memory: 127kB
- Bitmap Heap Scan on test (cost=56.48..2891.85 rows=1000
width=37) (actual time=80.230..132.479 rows=99641 loops=1)
Recheck Cond: (sections '{2}'::integer[])
- Bitmap Index Scan on test_sections_gist
(cost=0.00..56.23 rows=1000 width=0) (actual time=78.112..78.112 rows=99641
loops=1)
Index Cond: (sections '{2}'::integer[])
Total runtime: 175.960 ms
(9 rows)
Even if I drop GIST index i'm still getting wrong plan:
postgres=# drop index test_sections_gist;
DROP INDEX
postgres=# EXPLAIN ANALYZE SELECT * from test where sections '{2}' order
by value limit 1000;
QUERY PLAN
--
Limit (cost=4489.88..4492.38 rows=1000 width=37) (actual
time=116.637..117.088 rows=1000 loops=1)
- Sort (cost=4489.88..4492.38 rows=1000 width=37) (actual
time=116.635..116.857 rows=1000 loops=1)
Sort Key: value
Sort Method: top-N heapsort Memory: 127kB
- Bitmap Heap Scan on test (cost=1604.68..4440.05 rows=1000
width=37) (actual time=22.175..74.556 rows=99641 loops=1)
Recheck Cond: (sections '{2}'::integer[])
- Bitmap Index Scan on test_value_in2section_key
(cost=0.00..1604.43 rows=1000 width=0) (actual time=20.248..20.248
rows=99641 loops=1)
Total runtime: 117.261 ms
And only if I completely disable bitmap scan I get good fast plan (but with
exceptional high cost):
postgres=# set enable_bitmapscan to 0;
SET
postgres=# EXPLAIN ANALYZE SELECT * from test where sections '{2}' order
by value limit 1000;
QUERY
PLAN
Limit (cost=0.00..5392.87 rows=1000 width=37) (actual time=0.047..4.123
rows=1000 loops=1)
- Index Scan using test_value_in2section_key on test
(cost=0.00..5392.87 rows=1000 width=37) (actual time=0.044..3.552 rows=1000
loops=1)
Total runtime: 4.460 ms
I hope that test case will make my issue more clear.
Regards,
Maksym
On Mon, Jan 23, 2012 at 11:46 AM, Maxim Boguk maxim.bo...@gmail.com wrote:
On Mon, Jan 23, 2012 at
[GENERAL] Incomplete startup packet help needed
Immediately upon starting the server I get an incomplete startup packet log message. Just prior there is an autovacuum launcher started message. Any thoughts? David J. -- 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] Incomplete startup packet help needed
On 01/23/12 4:24 PM, David Johnston wrote: Immediately upon starting the server I get an incomplete startup packet log message. Just prior there is an autovacuum launcher started message. Any thoughts? based on the given information: something is probably wrong. -- john r pierceN 37, W 122 santa cruz ca mid-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] Incomplete startup packet help needed
On Monday, January 23, 2012 4:24:50 pm David Johnston wrote: Immediately upon starting the server I get an incomplete startup packet log message. Just prior there is an autovacuum launcher started message. Do you have other programs connecting to server on startup? Any thoughts? David J. -- 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] Incomplete startup packet help needed
On 1/23/2012 5:24 PM, David Johnston wrote: Immediately upon starting the server I get an incomplete startup packet log message. Just prior there is an autovacuum launcher started message. We've found that this message is printed in the log if a client makes a TCP connection to the PG server, but sends no traffic. For example it happens with our monitoring system, which checks that it can open a TCP connection to the PG port, then closes it. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PG 9.0 EBS Snapshot Backups on Slave
Hello, I am playing with a script that implements physical backups by snapshotting the EBS-backed software RAID. My basic workflow is this: 1. Stop PG on the slave 2. pg_start_backup on the master 3. On the slave: A. unmount the PG RAID B. snapshot each disk in the raid C. mount the PG RAID 4. pg_stop_backup 5. Restart PG on the slave Step 3 is actually quite fast, however, on the master, I end up seeing the following warning: WARNING: transaction log file 000100CC0076 could not be archived: too many failures I am guessing (I will confirm with timestamps later) this warning happens during steps 3A-3C, however my questions below stand regardless of when this failure occurs. It is worth noting that, the slave (seemingly) catches up eventually, recovering later log files with streaming replication current. Can I trust this state? Should I be concerned about this warning? Is it a simple blip that can easily be ignored, or have I lost data? From googling, it looks like retry attempts is not a configurable parameter (it appears to have retried a handful of times). If this is indeed a real problem, am I best off changing my archive_command to retain logs in a transient location when I am in snapshot mode, and then ship them in bulk once the snapshot has completed? Are there any other remedies that I am missing? Thank you very much for your time, Andrew Hannon -- 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] Incomplete startup packet help needed
On Jan 23, 2012, at 19:38, Adrian Klaver adrian.kla...@gmail.com wrote: On Monday, January 23, 2012 4:24:50 pm David Johnston wrote: Immediately upon starting the server I get an incomplete startup packet log message. Just prior there is an autovacuum launcher started message. Do you have other programs connecting to server on startup? Any thoughts? David J. -- Adrian Klaver adrian.kla...@gmail.com I have 3 external machines that hit this server plus Apache Tomcat on the same machine. I shutdown Tomcat during the restart. Possible a zombie (or hidden/forgotten) process is running though the log indicates local. Had been using pg_admin3 for testing and somehow ended up with a possible zombie process there so maybe... Version 9.0.x - can get the release a little later, running pg_dump at the moment. Still researching/learning but was using WAL shipping and the ship command was failing. In the target directory the most recent WAL files is only ~2MB in size (can't check the pg_xlog directory at the moment). My guess is size quota on target but cannot check at the moment. Turned off WAL archive and restarted the server. Restarted a couple of times until I remembered to shutdown all the other applications. Pg_dump seems to be running OK and was able to execute queries. Was still having difficulty getting my Tomcat software to connect but am unsure if I'm just missing something or what. No obvious messages in either log about that but cannot get back to it until the dump completes. Appreciate any help that can be given. Unfortunately I haven't focused enough attention on Linux (Ubuntu 10.4 LTE) and PostgreSQL administration as I'd like (education and/or professional services). Apparently that is going to change sooner than I had thought. David J. -- 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] PG 9.0 EBS Snapshot Backups on Slave
On Monday, January 23, 2012 07:54:16 PM Andrew Hannon wrote: It is worth noting that, the slave (seemingly) catches up eventually, recovering later log files with streaming replication current. Can I trust this state? Should be able to. The master will also actually retry the logs and eventually ship them all too, in my experience. -- 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] Incomplete startup packet help needed
GOOD NEWS On Jan 23, 2012, at 20:00, David Johnston pol...@yahoo.com wrote: On Jan 23, 2012, at 19:38, Adrian Klaver adrian.kla...@gmail.com wrote: On Monday, January 23, 2012 4:24:50 pm David Johnston wrote: Immediately upon starting the server I get an incomplete startup packet log message. Just prior there is an autovacuum launcher started message. Do you have other programs connecting to server on startup? Any thoughts? David J. -- Adrian Klaver adrian.kla...@gmail.com I have 3 external machines that hit this server plus Apache Tomcat on the same machine. I shutdown Tomcat during the restart. Possible a zombie (or hidden/forgotten) process is running though the log indicates local. Had been using pg_admin3 for testing and somehow ended up with a possible zombie process there so maybe... Version 9.0.x - can get the release a little later, running pg_dump at the moment. Still researching/learning but was using WAL shipping and the ship command was failing. In the target directory the most recent WAL files is only ~2MB in size (can't check the pg_xlog directory at the moment). My guess is size quota on target but cannot check at the moment. Turned off WAL archive and restarted the server. Restarted a couple of times until I remembered to shutdown all the other applications. Pg_dump seems to be running OK and was able to execute queries. Was still having difficulty getting my Tomcat software to connect but am unsure if I'm just missing something or what. No obvious messages in either log about that but cannot get back to it until the dump completes. Appreciate any help that can be given. Unfortunately I haven't focused enough attention on Linux (Ubuntu 10.4 LTE) and PostgreSQL administration as I'd like (education and/or professional services). Apparently that is going to change sooner than I had thought. David J. Looking at my process viewer I saw a Java process running (with Catalina...) even though I supposedly shutdown Tomcat. Since Tomcat wasn't working anyway I decided to just kill the process then restart PostgreSQL...no more spurious error message. Restarted Tomcat and I can now hit my Servlet. Just need to get the base backup and WAL shipping enabled again. Assuming since I disabled it, and just ran a pg_dump while everything was disconnected, that I can basically throw out the shipped WAL files? Still kinda at a loss for root cause and deciding whether there still may be an underlying problem. Thoughts are welcome but I'm likely to seek professional help on this one regardless. Thanks for listening. David J. -- 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] Incomplete startup packet help needed
On Monday, January 23, 2012 5:00:17 pm David Johnston wrote: On Jan 23, 2012, at 19:38, Adrian Klaver adrian.kla...@gmail.com wrote: On Monday, January 23, 2012 4:24:50 pm David Johnston wrote: Immediately upon starting the server I get an incomplete startup packet log message. Just prior there is an autovacuum launcher started message. Do you have other programs connecting to server on startup? Any thoughts? David J. I have 3 external machines that hit this server plus Apache Tomcat on the same machine. I shutdown Tomcat during the restart. Possible a zombie (or hidden/forgotten) process is running though the log indicates local. Had been using pg_admin3 for testing and somehow ended up with a possible zombie process there so maybe... Version 9.0.x - can get the release a little later, running pg_dump at the moment. Still researching/learning but was using WAL shipping and the ship command was failing. In the target directory the most recent WAL files is only ~2MB in size (can't check the pg_xlog directory at the moment). My guess is size quota on target but cannot check at the moment. Turned off WAL archive and restarted the server. Restarted a couple of times until I remembered to shutdown all the other applications. Pg_dump seems to be running OK and was able to execute queries. Was still having difficulty getting my Tomcat software to connect but am unsure if I'm just missing something or what. No obvious messages in either log about that but cannot get back to it until the dump completes. Appreciate any help that can be given. Unfortunately I haven't focused enough attention on Linux (Ubuntu 10.4 LTE) and PostgreSQL administration as I'd like (education and/or professional services). Apparently that is going to change sooner than I had thought. Alright alot going on. Searching for the error message tends to indicate what others have said, there is something basically pinging the server with 'empty' packets. Given everything you going on right now I would probably wait until the server has attained a more quiescent state before tracking down the culprit. At guess it not something that is server threatening, but worthy of attention. Once the dump is complete and if it is possible, shut down the server. Go through and make sure the programs hitting the server are shut down, for now at least that probably should include the WAL archiving. Start the server up, tail -f the log and then start up the client programs one at a time and see if any of them tickle the error. David J. -- 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] update with from
On 01/23/2012 07:10 PM, Adrian Klaver wrote: On Monday, January 23, 2012 7:32:35 am Sim Zacks wrote: On 01/23/2012 05:13 PM, Adrian Klaver wrote: When I throw in code to make the select only return the correct rows The select statement takes 9 secs by itself: select a.partid,a.deliverywks from poparts a where popartid in ( select b.popartid from poparts b join pos c using(poid) join stock.lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate d.issuedate-7 AND b.unitprice 0::numeric AND b.quantity = 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) and b.partid=a.partid order by b.partid,b.unitprice, b.deliverywks limit 1 ) To clarify what I posted earlier, my suggestion was based on rewriting the second query as: select b.partid,b.deliverywks b.popartid from poparts b join pos c using(poid) join stock.lastrfqdateperpart d using(partid) where c.isrfq and c.issuedate d.issuedate-7 AND b.unitprice 0::numeric AND b.quantity = 100::numeric AND c.postatusid = ANY (ARRAY[40, 41]) order by b.partid,b.unitprice, b.deliverywks limit 1 I may be missing the intent of your original query, but I think the above gets to the same result without the IN. My first query returns all rows of each part ordered such so that the row I want to actually update the table with is last. This query returns 12000 rows, for the 600 parts I want to update. My second query with the limit within the subselect gets 1 row per part. This returns 600 rows, 1 row for each part I want to update. Your suggestion would only return one row. See http://www.pgsql.cz/index.php/PostgreSQL_SQL_Tricks#Select_first_n_rows_from_group for reference. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
