Re: [GENERAL] Running untrusted sql safely?
Scott Marlowe wrote: On Sun, Feb 15, 2009 at 4:39 PM, Christophe wrote: On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote: I just hoping for some confirmation that the permissions based approach did not have some holes in it that I am not seeing. Another possibility is to create a set of functions that contain the query operations you would like to allow, isolate those in a schema, and make that schema the only thing accessible to the (semi-)trusted users. I can see that getting complex real fast in a big operation, but for a database that runs a few big reporting queries every day or sits on an intranet would be workable. ... And to actually answer Christophes question: yes, granting only SELECT to a few tables is enough to prevent them doing anything else in the database. But watch out for the default permissions on the public schema of all the databases the users are able to connect to. Regards Tino smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Check for an empty result
Hi Andreas! --- On Fri, 2/13/09, A. Kretschmer wrote: > In response to Eus : > > Hi Ho! > > > > Is there a way to check whether or not a subquery > returns an empty result set? > > You can use EXISTS for that: > > -- empty result > test=*# select * from (select 1 where 1=2) foo; > ?column? > -- > (0 rows) That's good that it can be used in FROM phrase too besides WHERE phrase. > -- check if a result exists > test=*# select exists(select * from (select 1 where 1=2) > foo); > ?column? > -- > f > (1 row) > > test=*# select exists(select * from (select 1 where 1=1) > foo); > ?column? > -- > t > (1 row) Even in SELECT phrase? That's great! Thank you for the information. > Regards, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: > -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA > http://wwwkeys.de.pgp.net Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- 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] Check for an empty result
Hi Craig! --- On Fri, 2/13/09, Craig Ringer wrote: > Eus wrote: > > Hi Ho! > > > > Is there a way to check whether or not a subquery > returns an empty result set? > > "EXISTS" > > SELECT blah FROM blah WHERE EXISTS (SELECT 1 FROM tablename > WHERE ...); Thank you. Previously I tried: "... WHERE (...) IS NULL;". Of course, it didn't work. > > postgre check "empty result set" > > It's not "postgre". It's PostgreSQL, or > "postgres". This matters when > you're searching. Oh, okay. Thanks for telling me. > -- > Craig Ringer Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computing devices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445 -- 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] Running untrusted sql safely?
On Sun, Feb 15, 2009 at 4:39 PM, Christophe wrote: > > On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote: > >> I just hoping for some confirmation that the permissions based approach >> did not have some holes in it that I am >> not seeing. > > Another possibility is to create a set of functions that contain the query > operations you would like to allow, isolate those in a schema, and make that > schema the only thing accessible to the (semi-)trusted users. I can see that getting complex real fast in a big operation, but for a database that runs a few big reporting queries every day or sits on an intranet would be workable. Another option is to create preferred views. These server two purposes, one they make life easier for your users, because they don't have to join 7 tables to look at the data anymore, the view does that for them, or whatever makes the queries ugly. They don't have to worry about accidentally creating an unconstrained join by accident unless they step outside the views. The users who know how to writer bigger and better queries and test them with explain analyze are given view creation ability, and it's a self sustaining environment. I've found users faced with lots of tables very receptive to views to make their job simpler, so there's usually a pretty good buy in on it. -- 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] Attempting to connect
Woohoo! Glad you got it working... -- 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] Running untrusted sql safely?
On Feb 15, 2009, at 2:47 PM, Stuart McGraw wrote: I just hoping for some confirmation that the permissions based approach did not have some holes in it that I am not seeing. Another possibility is to create a set of functions that contain the query operations you would like to allow, isolate those in a schema, and make that schema the only thing accessible to the (semi-)trusted users. Generally, I try to design things so that web servers and other clients who could potentially be compromised don't execute full- function SQL directly, but go through functions instead. -- 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] Attempting to connect
Finally Success Thanks everyone Bob - Original Message - From: "John R Pierce" To: "Bob Pawley" Cc: "PostgreSQL" Sent: Sunday, February 15, 2009 3:05 PM Subject: Re: [GENERAL] Attempting to connect # IPv4 local connections: host all all 0.0.0.0 255.255.255.255 md5 host all all127.0.0.1/32 md5 oh. that first HOST line will only allow IP 0.0.0.0, hardly likely to be a valid address. if you want to allow connections from ALL IPs, use 0.0.0.0/0 or 0.0.0.0 0.0.0.0 the second line allows connections to localhost only. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Running untrusted sql safely?
Scott Marlowe wrote: On Sun, Feb 15, 2009 at 3:09 PM, Stuart McGraw wrote: John R Pierce wrote: Stuart McGraw wrote: What is the best way to run an arbitrary query received from an untrusted source, safely? (I want a web page form with a textbox that a user can enter an arbitrary sql statement, then run it . just keep http://xkcd.com/327/ in mind. Yes, exactly what I would like some advice on avoiding! :-) Your first idea, to allow it to connect via a read only user is a good start. Another thing you can do is explain the query, then see what the cost is according to first line in the explain output that has it. explain select * from a; QUERY PLAN -- Seq Scan on a (cost=0.00..29.40 rows=1940 width=12) Grep out that first line, look for the number on the right of the .. and if it's over some predetermined threshold then refuse to run it. The "29.40"? That's an interesting idea that would not have occurred to me, thanks! It's like herding cats. There's only so much you can do to prevent someone who's running sql on your database from DOSing the server. In my case access to arbitrary sql statements will be limited to a relatively small set of authenticated users so a social/administrative approach to DoS problems will be OK I think. But for protection against data deletion/corruption I would like a stronger guarantee. I just hoping for some confirmation that the permissions based approach did not have some holes in it that I am not seeing. -- 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] Attempting to connect
# IPv4 local connections: host all all 0.0.0.0 255.255.255.255 md5 host all all127.0.0.1/32 md5 oh. that first HOST line will only allow IP 0.0.0.0, hardly likely to be a valid address. if you want to allow connections from ALL IPs, use 0.0.0.0/0 or 0.0.0.0 0.0.0.0 the second line allows connections to localhost only. -- 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] Attempting to connect
On Sunday 15 February 2009 2:19:13 pm Adrian Klaver wrote: > On Sunday 15 February 2009 1:41:14 pm Bob Pawley wrote: > > Hi > > > > I do need some help here. > > > > I am still having trouble making a remote connection. The 'Server doesn't > > listen' message comes up. > > > > The config files have been changed as shown below. > > > > Postgresql.conf > > > > #listen_addresses = '*' > > > > pg_hba > > > > # TYPE DATABASE USER CIDR-ADDRESs IP-Mask METHOD > > > > # IPv4 local connections: > > host all all 0.0.0.0 > > 255.255.255.255 md5 > > host all all127.0.0.1/32 > > md5 > > # IPv6 local connections: > > host all all ::1/128 > > md5 > > > > I disconnect the firewall during the connection attempt. > > > > After changes to the above file I run - pg_ctl reload - and stop/start > > the server. > > > > The last few log entries follows. > > > > 2009-02-15 13:01:54 PST LOG: loaded library > > "$libdir/plugins/plugin_debugger.dll" > > 2009-02-15 13:02:10 PST LOG: loaded library > > "$libdir/plugins/plugin_debugger.dll" > > 2009-02-15 13:10:39 PST LOG: loaded library > > "$libdir/plugins/plugin_debugger.dll" > > 2009-02-15 13:16:16 PST LOG: received SIGHUP, reloading configuration > > files 2009-02-15 13:16:52 PST LOG: loaded library > > "$libdir/plugins/plugin_debugger.dll" > > > > The latest couple of attempts didn't leave a log behind. > > > > Thanks > > > > Bob > > The server is running? Can you connect locally? I don't know how you have > logging setup, but when I stop/start a server it generates a new log file. > Are you sure you are looking at the most recent log? > > -- > Adrian Klaver > akla...@comcast.net Just saw this: host all all 0.0.0.0 255.255.255.255 md5 You should change this to 0.0.0.0/0 and ditch the the netmask. See examples at bottom of page below for more information- http://www.postgresql.org/docs/8.3/interactive/auth-pg-hba-conf.html -- Adrian Klaver akla...@comcast.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] Attempting to connect
On Sun, Feb 15, 2009 at 3:29 PM, Bob Pawley wrote: > The log file states "parameter "listen-adress" cannot be changed after > server start; conf change ignored. > > I am getting this log entry after saving the file and reloading with the > server shutdown. > > Does anyone have a procedure for making changes to this file? You need to restart, not reload, the database server. -- 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] Attempting to connect
The log file states "parameter "listen-adress" cannot be changed after server start; conf change ignored. I am getting this log entry after saving the file and reloading with the server shutdown. Does anyone have a procedure for making changes to this file? Bob - Original Message - From: "John R Pierce" To: "Bob Pawley" ; Sent: Sunday, February 15, 2009 1:44 PM Subject: Re: [GENERAL] Attempting to connect Bob Pawley wrote: Hi I do need some help here. I am still having trouble making a remote connection. The 'Server doesn't listen' message comes up. The config files have been changed as shown below. Postgresql.conf #listen_addresses = '*' you'd need to remove that # (comment) from the front if you want it to pay attention -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Running untrusted sql safely?
On Sun, Feb 15, 2009 at 3:09 PM, Stuart McGraw wrote: > John R Pierce wrote: >> >> Stuart McGraw wrote: >>> >>> What is the best way to run an arbitrary query received from an untrusted >>> source, safely? >>> (I want a web page form with a textbox that >>> a user can enter an arbitrary sql statement, >>> then run it . >> >> just keep http://xkcd.com/327/ in mind. > > Yes, exactly what I would like some advice on avoiding! :-) Your first idea, to allow it to connect via a read only user is a good start. Another thing you can do is explain the query, then see what the cost is according to first line in the explain output that has it. explain select * from a; QUERY PLAN -- Seq Scan on a (cost=0.00..29.40 rows=1940 width=12) Grep out that first line, look for the number on the right of the .. and if it's over some predetermined threshold then refuse to run it. It's like herding cats. There's only so much you can do to prevent someone who's running sql on your database from DOSing the server. -- 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] Array in nested query
Thanks, Osvaldo and Fernando - your solution works! > -Original Message- > From: Osvaldo Kussama [mailto:osvaldo.kuss...@gmail.com] > Sent: Saturday, February 14, 2009 8:24 PM > To: Ken Winter > Subject: Re: [GENERAL] Array in nested query > > 2009/2/14 Ken Winter : > > I'm trying to look up the columns in a constraint in pg_catalog (of > > PostgreSQL 8.0.x). I can't figure out how to "join" the elements of the > > array that lists the 'attnum's of the columns in the table to the > 'conkey' > > array in the constraint definition (see > > http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html and > > http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html ). > > > > > > > > The problem is in the last line of this query: > > > > > > > > SELECT a.attname AS name > > > > FROM pg_catalog.pg_class t, pg_catalog.pg_attribute a > > > > WHERE t.oid = 3626912 > > > > AND a.attrelid = t.oid > > > > AND a.attnum = any (select conkey FROM pg_catalog.pg_constraint > WHERE > > oid = 3708025) > > > > > > > > I have tried all the variations on this syntax that I can think of, > after > > plowing through all the documentation of arrays I can find in > > http://www.postgresql.org/docs/8.0/static/index.html, and none of them > work. > > > > > I've a similar problem. > Try explicit cast and an extra parenthesis: > > SELECT a.attname AS name > FROM pg_catalog.pg_class t, pg_catalog.pg_attribute a > WHERE t.oid = 3626912 >AND a.attrelid = t.oid >AND a.attnum = any ((select conkey FROM pg_catalog.pg_constraint > WHERE oid = 3708025)::smallint[]); > > Osvaldo -- 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_hba reload
--- On Sat, 2/14/09, Bob Pawley wrote: > From: Bob Pawley > Subject: Re: [GENERAL] pg_hba reload > To: "John R Pierce" , "PostgreSQL" > > Date: Saturday, February 14, 2009, 11:31 PM > I'm running 8.3 on Windows XP. > > I input > pg_ctl reload -D c:\program > files\postgresql\8.3\data- or any > combination thereof, with or without brackets and the server > is running. > > The return is - 'pg_ctl: too many command-line > arguments'. > Maybe the problem is te blank space in Program Files test with pg_ctl reload -D "c:\program files\postgresql\8.3\data" -- 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] Attempting to connect
On Sunday 15 February 2009 1:41:14 pm Bob Pawley wrote: > Hi > > I do need some help here. > > I am still having trouble making a remote connection. The 'Server doesn't > listen' message comes up. > > The config files have been changed as shown below. > > Postgresql.conf > > #listen_addresses = '*' > > pg_hba > > # TYPE DATABASE USER CIDR-ADDRESs IP-Mask METHOD > > # IPv4 local connections: > host all all 0.0.0.0 > 255.255.255.255 md5 > host all all127.0.0.1/32 > md5 > # IPv6 local connections: > host all all ::1/128 > md5 > > I disconnect the firewall during the connection attempt. > > After changes to the above file I run - pg_ctl reload - and stop/start the > server. > > The last few log entries follows. > > 2009-02-15 13:01:54 PST LOG: loaded library > "$libdir/plugins/plugin_debugger.dll" > 2009-02-15 13:02:10 PST LOG: loaded library > "$libdir/plugins/plugin_debugger.dll" > 2009-02-15 13:10:39 PST LOG: loaded library > "$libdir/plugins/plugin_debugger.dll" > 2009-02-15 13:16:16 PST LOG: received SIGHUP, reloading configuration > files 2009-02-15 13:16:52 PST LOG: loaded library > "$libdir/plugins/plugin_debugger.dll" > > The latest couple of attempts didn't leave a log behind. > > Thanks > > Bob The server is running? Can you connect locally? I don't know how you have logging setup, but when I stop/start a server it generates a new log file. Are you sure you are looking at the most recent log? -- Adrian Klaver akla...@comcast.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] Running untrusted sql safely?
John R Pierce wrote: Stuart McGraw wrote: What is the best way to run an arbitrary query received from an untrusted source, safely? (I want a web page form with a textbox that a user can enter an arbitrary sql statement, then run it . just keep http://xkcd.com/327/ in mind. Yes, exactly what I would like some advice on avoiding! :-) -- 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] Attempting to connect
Removed # Reloaded postmaster, restarted server. Server still isn't listening. Bob - Original Message - From: "John R Pierce" To: "Bob Pawley" ; Sent: Sunday, February 15, 2009 1:44 PM Subject: Re: [GENERAL] Attempting to connect Bob Pawley wrote: Hi I do need some help here. I am still having trouble making a remote connection. The 'Server doesn't listen' message comes up. The config files have been changed as shown below. Postgresql.conf #listen_addresses = '*' you'd need to remove that # (comment) from the front if you want it to pay attention -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] Attempting to connect
Bob Pawley wrote: Hi I do need some help here. I am still having trouble making a remote connection. The 'Server doesn't listen' message comes up. The config files have been changed as shown below. Postgresql.conf #listen_addresses = '*' you'd need to remove that # (comment) from the front if you want it to pay attention -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Attempting to connect
Hi I do need some help here. I am still having trouble making a remote connection. The 'Server doesn't listen' message comes up. The config files have been changed as shown below. Postgresql.conf #listen_addresses = '*' pg_hba # TYPE DATABASE USER CIDR-ADDRESs IP-Mask METHOD # IPv4 local connections: host all all 0.0.0.0 255.255.255.255 md5 host all all127.0.0.1/32 md5 # IPv6 local connections: host all all ::1/128 md5 I disconnect the firewall during the connection attempt. After changes to the above file I run - pg_ctl reload - and stop/start the server. The last few log entries follows. 2009-02-15 13:01:54 PST LOG: loaded library "$libdir/plugins/plugin_debugger.dll" 2009-02-15 13:02:10 PST LOG: loaded library "$libdir/plugins/plugin_debugger.dll" 2009-02-15 13:10:39 PST LOG: loaded library "$libdir/plugins/plugin_debugger.dll" 2009-02-15 13:16:16 PST LOG: received SIGHUP, reloading configuration files 2009-02-15 13:16:52 PST LOG: loaded library "$libdir/plugins/plugin_debugger.dll" The latest couple of attempts didn't leave a log behind. Thanks Bob -- 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] Running untrusted sql safely?
Stuart McGraw wrote: What is the best way to run an arbitrary query received from an untrusted source, safely? (I want a web page form with a textbox that a user can enter an arbitrary sql statement, then run it . just keep http://xkcd.com/327/ in mind. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Running untrusted sql safely?
What is the best way to run an arbitrary query received from an untrusted source, safely? (I want a web page form with a textbox that a user can enter an arbitrary sql statement, then run it but I want to prevent therm from changing anything or escaping postgresql and executing system commands. I.e., it is intended to allow for searching only. I understand and accept that resource hogging queries could submitted constituting a DoS attack but I will deal with that in other ways.) I am thinking the running the query on a connection with a role that gives only select privileges might be sufficient. Is it? Any things I need to watch out for? Any other or better ways to do this? -- 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] partial unique index and the planner
Michal Politowski writes: > Is it normal that plans using a scan on a partial unique index > estimate that much more than one row is returned? There isn't currently any special logic to recognize that case; the estimate is just whatever is going to come out of the normal statistics-based estimation. I'm unsure how hard it'd be to improve the situation. If we've already identified relevant partial indexes before any of the stats code has to run then it'd be pretty easy, but that might be a bit fragile. Anyway, the usual advice for such cases is to see if raising the statistics target helps. 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] partial unique index and the planner
2009/2/15 Michal Politowski : > I'm using PostgreSQL 8.3. > Is it normal that plans using a scan on a partial unique index > estimate that much more than one row is returned? > > Eg. I see: > -> Bitmap Index Scan on tmp_idx_oss_archive_object_id_current > (cost=0.00..3.12 rows=4189 width=0) > where the tmp_idx_oss_archive_object_id_current index is a partial unique > index. > > The estimated row count would be correct for the whole table but obviously > not for > the part covered by the unique index. > > This happens to be a problem in this case because then the planner > prefers a sequence scan on a table joined to this one and a hash join to an > index scan > and a nested loop join. Which takes hundreds of milliseconds instead of > one, so setting enable_hashjoin to false increases performance immensely. Have you run analyze since creating the unique partial index? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] partial unique index and the planner
I'm using PostgreSQL 8.3. Is it normal that plans using a scan on a partial unique index estimate that much more than one row is returned? Eg. I see: -> Bitmap Index Scan on tmp_idx_oss_archive_object_id_current (cost=0.00..3.12 rows=4189 width=0) where the tmp_idx_oss_archive_object_id_current index is a partial unique index. The estimated row count would be correct for the whole table but obviously not for the part covered by the unique index. This happens to be a problem in this case because then the planner prefers a sequence scan on a table joined to this one and a hash join to an index scan and a nested loop join. Which takes hundreds of milliseconds instead of one, so setting enable_hashjoin to false increases performance immensely. -- Michał Politowski Talking has been known to lead to communication if practiced carelessly. -- 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 do I set the schema search path in a datasource config for a connection pool?
On Sun, 15 Feb 2009 08:47:36 -0700 Scott Marlowe wrote: > On Sun, Feb 15, 2009 at 5:58 AM, Bjørn T Johansen wrote: > > I can't find a way to do this, what am I missing? > > You can either issue the command when you open the connection, alter > the user the connection uses, or alter the database to default to that > search path. > > alter user bubba set search_path='path1','path2'; > Yes, alter user worked fine... Thx... :) BTJ -- 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 do I set the schema search path in a datasource config for a connection pool?
On Sun, Feb 15, 2009 at 5:58 AM, Bjørn T Johansen wrote: > I can't find a way to do this, what am I missing? You can either issue the command when you open the connection, alter the user the connection uses, or alter the database to default to that search path. alter user bubba set search_path='path1','path2'; -- 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] Array in nested query
2009/2/15 Fernando Moreno : > What error are you getting? > > I tried your query and I had to add an explicit cast to smallint[] to > make it work. Like this: > > ... a.attnum = any ((select conkey FROM pg_catalog.pg_constraint WHERE >> oid = 3708025)::smallint[]); > > It seems strange to me, I didn't expect the ANY clause to need that > cast. Or maybe I'm missing something. > Look this thread: http://archives.postgresql.org/pgsql-hackers/2008-12/msg00496.php Osvaldo -- 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() in insert statements
Onno Molenkamp wrote:
> Hi,
>
> I recently upgraded a database from 8.1.11 to 8.3.6, and I noticed the
> following statement stopped working:
>
> insert into test (b) select currval('test_a_seq'::regclass)
It's generally a REALLY bad idea to mix `nextval' and `currval' use on
the same sequence in a single SQL statement.
Personally, in the one case in the project I'm working on where I DO
need to INSERT a generated ID in two places I use a trigger to take care
of it.
--
Craig Ringer
--
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 do I set the schema search path in a datasource config for a connection pool?
I can't find a way to do this, what am I missing? Regards, BTJ -- --- Bjørn T Johansen b...@havleik.no --- Someone wrote: "I understand that if you play a Windows CD backwards you hear strange Satanic messages" To which someone replied: "It's even worse than that; play it forwards and it installs Windows" --- -- 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] select max from subquery
Oleg Bartunov wrote: > > yOn Sat, 14 Feb 2009, searchelite wrote: > >> >> Dear All >> >> i gave this kind of query >> >> select max(foo) from (select some statement) as foo >> >> but it gave me this error >> ERROR: function max(record) does not exist >> >> >> any help how i can select max value from suqbuery in main query >> > > like this: > > select max(foo.qq) from (select 1 as qq) as foo; >^^ ^^ > >> thanks >> > > Regards, > Oleg > _ > Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), > Sternberg Astronomical Institute, Moscow University, Russia > Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ > phone: +007(495)939-16-83, +007(495)939-23-83 > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > thanks oleg for your reply..that's what i need ;) -- View this message in context: http://www.nabble.com/select-max-from-subquery-tp22011562p22021619.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] Array in nested query
On Sat, Feb 14, 2009 at 05:36:11PM -0500, Ken Winter wrote: >I'm trying to look up the columns in a constraint in pg_catalog (of >PostgreSQL 8.0.x). I can't figure out how to "join" the elements of the >array that lists the `attnum's of the columns in the table to the `conkey' >array in the constraint definition (see >http://www.postgresql.org/docs/8.0/static/catalog-pg-constraint.html and >http://www.postgresql.org/docs/8.0/static/catalog-pg-attribute.html ). > >The problem is in the last line of this query: > >SELECT a.attname AS name > >FROM pg_catalog.pg_class t, pg_catalog.pg_attribute a > >WHERE t.oid = 3626912 > >AND a.attrelid = t.oid > >AND a.attnum = any (select conkey FROM pg_catalog.pg_constraint WHERE >oid = 3708025) SELECT a.attname AS name FROM pg_catalog.pg_class t, pg_catalog.pg_attribute a WHERE t.oid = 3626912 AND a.attrelid = t.oid AND a.attnum IN (SELECT conkey FROM pg_catalog.pg_constraint WHERE oid = 3708025) >I have tried all the variations on this syntax that I can think of, after >plowing through all the documentation of arrays I can find in >http://www.postgresql.org/docs/8.0/static/index.html, and none of them >work. > >Any ideas? > >~ TIA > >~ Ken Regards, Gerhard signature.asc Description: Digital signature
Re: [GENERAL] Slow update
Alban Hertroys wrote:
> On Feb 9, 2009, at 2:07 PM, Grzegorz Jaśkiewicz wrote:
>
>> On Mon, Feb 9, 2009 at 12:50 PM, Herouth Maoz
>> wrote:
>>> I hope someone can clue me in based on the results of explain analyze.
>>
>> Did you have a chance to run vmstat on it, and post it here ? Maybe -
>> if db resides on the same disc with everything else, something
>> (ab)uses that much io, and it has to wait.
>> Also, I don't know - but personaly I didn't like the line in explain:
>>
>> -> Bitmap Index Scan on billing_msisdn_sme_reference
>> (cost=0.00..24.70 rows=389 width=0) (actual time=2
>> 1.418..21.418 rows=252 loops=151332)
>>Index Cond: ((b.msisdn)::text =
>> (("substring"((rb.msisdn)::text, 1, 0) || '972'::text) || "substrin
>> g"((rb.msisdn)::text, 2)))
>>
>> But the cost is next to none, so that's not it.
>
>
> Actually, it's inside a nested loop and if I read correctly it gets
> looped over 151332 times. That means it takes 151332 * (21.418 -
> 1.418) = 3026640 ms, which is almost 12% of the total time.
>
> The biggie seems to be the bitmap heap scan on rb though. The row
> estimates for that one are way off (estimated 549 rows vs actual 151332).
>
> Alban Hertroys
>
To be quite honest, I'm not sure exactly how to read this plan, and what
the row values mean. The issue here is that sometimes the query works in
reasonable time, and sometimes it takes half a day. Of course, this may
be because the data size is different, but I don't know which part of
the plan tells me that.
What do rows vs. loops signify? How can the estimate be so far off if
I'm running analyze on all the tables right after I make any big updates
to them?
I find it hard to believe that the problem is with the complex
comparison caused by the different formats of the fields in rb and in
billing. This should add a constant multiplier to the time it takes to
run the query, but not cause the query to run one time in 5 minutes, and
the next day in 12 hours!
Thanks for the assistance.
Herouth
Here is the plan collected tonight, which took a reasonable amount of
time (5 minutes). It seems to be a totally different plan, isn't it?:
QUERY PLAN
-
Merge Join (cost=1157750.08..1167132.31 rows=1 width=210) (actual
time=238247.983..239980.264 rows=111676 loops=1)
Merge Cond: ((rb.reference = b.user_reference) AND
((rb.sms_user)::text = (b.user_id)::text) AND
"substring"((rb.msisdn)::text, 1, 0) || '972'::text) ||
"substring"((rb.msisdn)::text, 2))) = (b.msisdn)::text))
-> Sort (cost=31137.76..31141.31 rows=1423 width=198) (actual
time=117858.431..117932.544 rows=111676 loops=1)
Sort Key: rb.reference, rb.sms_user,
((("substring"((rb.msisdn)::text, 1, 0) || '972'::text) ||
"substring"((rb.msisdn)::text, 2)))
Sort Method: quicksort Memory: 30941kB
-> Bitmap Heap Scan on rb (cost=26968.95..31063.23 rows=1423
width=198) (actual time=113615.187..116935.502 rows=111676 loops=1)
Recheck Cond: ((delivered = 0) AND (time_stamp >=
'2009-02-12 00:00:00'::timestamp without time zone))
Filter: ((NOT mo_billed) AND (system_id <> 6))
-> BitmapAnd (cost=26968.95..26968.95 rows=1423
width=0) (actual time=113454.761..113454.761 rows=0 loops=1)
-> Bitmap Index Scan on rb_delivered_ind
(cost=0.00..2522.46 rows=69896 width=0) (actual time=9358.397..9358.397
rows=150651 loops=1)
Index Cond: (delivered = 0)
-> Bitmap Index Scan on rb_timestamp_ind
(cost=0.00..24445.53 rows=213475 width=0) (actual
time=104091.620..104091.620 rows=303308 loops=1)
Index Cond: (time_stamp >= '2009-02-12
00:00:00'::timestamp without time zone)
-> Sort (cost=1117952.26..1120779.49 rows=1130889 width=50) (actual
time=119485.709..120263.045 rows=756135 loops=1)
Sort Key: b.user_reference, b.user_id, b.msisdn
Sort Method: external sort Disk: 60976kB
-> Bitmap Heap Scan on billing b (cost=36754.98..1004246.88
rows=1130889 width=50) (actual time=24409.448..101034.765 rows=896474
loops=1)
Recheck Cond: (time_arrived >= '2009-02-12
00:00:00'::timestamp without time zone)
-> Bitmap Index Scan on billing_time_arrived
(cost=0.00..36472.26 rows=1130889 width=0) (actual
time=23936.245..23936.245 rows=1166881 loops=1)
Index Cond: (time_arrived >= '2009-02-12
00:00:00'::timestamp without time zone)
Total runtime: 307958.152 ms
(21 rows)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
