Re: [GENERAL] Bug, Feature, or what else?
2013/2/8 Andreas Kretschmer andr...@a-kretschmer.de How can i drop a user as SUPERUSER (!) with all privileges? According to the docs: http://www.postgresql.org/docs/current/interactive/sql-droprole.html A role cannot be removed if it is still referenced in any database of the cluster; an error will be raised if so. Before dropping the role, you must drop all the objects it owns (or reassign their ownership) and revoke any privileges the role has been granted. The REASSIGN OWNEDhttp://www.postgresql.org/docs/current/interactive/sql-reassign-owned.html and DROP OWNEDhttp://www.postgresql.org/docs/current/interactive/sql-drop-owned.html commands can be useful for this purpose. -- Victor Y. Yegorov
Re: [GENERAL] DEFERRABLE NOT NULL constraint
2013/2/5 Darren Duncan dar...@darrenduncan.net: I'd like to know what value there is in making NOT NULL and CHECK deferrable. Consider such schema sample: - you have tables “groups” and “group_items” - each group must have at least one item - each group must have a “master” item, that is denoted in groups.master_item_id column - groups.group_id, groups.master_item_id, group_items.item_id and group_items.group_id should be NOT NULL - you use “serial” type for the KEY columns Now, when you're creating a new group: - you cannot insert a row into the groups, as master_item_id is not yet known and NULL is not allowed; - you cannot insert a row into the group_items, as you need to know group_id, FK can be deferred, but NULL is not allowed. All this works pretty good if one don't use “serial” type for the keys and explicitly calls nextval() on the corresponding sequences first. -- Victor Y. Yegorov -- 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] grouping consecutive records
2013/2/4 Morus Walter morus.walter...@googlemail.com: I'd like to merge all consecutive records (ordered by sort, user_id) having the same value in user_id and key and keep the first/last value of sort of the merged records (and probably some more values from the first or last merged record). So the result should be something like user_id, key, sort_first, sort_last 1, 'foo', 1, 2 1, 'bar', 3, 3 1, 'foo', 4, 6 1, 'bla', 7, 7 2, 'bar', 1, 1 2, 'foo', 2, 3 2, 'bla', 4, 4 This example corresponds to the ORDER BY user_id, sort while you claim you need to ORDER BY sort, user_id. I will explain this for the ordering that matches your sample. You need to group your data, but you should first create an artificial grouping column. First, detect ranges of your buckets: WITH ranges AS ( SELECT id, user_id, key, sort, CASE WHEN lag(key) OVER (PARTITION BY user_id ORDER BY user_id, sort) = key THEN NULL ELSE 1 END r FROM foo ) SELECT * FROM ranges; Here each time a new “range” is found, «r» is 1, otherwise it is NULL. Now, form your grouping column: WITH ranges AS ( SELECT id, user_id, key, sort, CASE WHEN lag(key) OVER (PARTITION BY user_id ORDER BY user_id, sort) = key THEN NULL ELSE 1 END r FROM foo ) , groups AS ( SELECT id, user_id, key, sort, r, sum(r) OVER (ORDER BY user_id, sort) grp FROM ranges ) SELECT * FROM groups; Here sum() is used as running total to produce new “grp” values. Final query looks like this: WITH ranges AS ( SELECT id, user_id, key, sort, CASE WHEN lag(key) OVER (PARTITION BY user_id ORDER BY user_id, sort) = key THEN NULL ELSE 1 END r FROM foo ) , groups AS ( SELECT id, user_id, key, sort, r, sum(r) OVER (ORDER BY user_id, sort) grp FROM ranges ) SELECT min(user_id) user_id, min(key) key, min(sort) sort_first, max(sort) sort_last FROM groups GROUP BY grp ORDER BY user_id,sort_first; Based on this SO answer: http://stackoverflow.com/a/10624628/1154462 -- Victor Y. Yegorov -- 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] Logging successful SELECTS?
2013/1/24 Matthew Vernon matthew.ver...@sac.ac.uk: I can get postgres to log unsuccessful queries, including the user who wrote them, but I'm missing how to get postgres to log the successful queries too (I don't need a store of the answer, just the query itself). How do I do this? You can use either log_min_duration_statement or log_statement. http://www.postgresql.org/docs/current/interactive/runtime-config-logging.html I would recommend using the first one, logging all statements that took longer then, say, 300ms. Otherwise logs might become too huge to manage. -- Victor Y. Yegorov -- 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] Problem with aborting entire transactions on error
2012/12/10 Thomas Kellerer spam_ea...@gmx.net Zbigniew, 10.12.2012 04:20: Yes, I read about using savepoints - but I think we agree, it's just cumbersome workaround - and not real solution, It might be a bit cumbersome, but it *is* a proper solution to the problem - not a workaround. Writing scripts for ORACLE's sqlplus, I have the option to write whenever sqlerror exit sql.sqlcode; and get the behavior similar to setting psql's ON_ERROR_STOP variable. On the other hand, it is possible to write whenever sqlerror continue; and this will make ORACLE to process all the statements inide the script, ignoring all errors. This is a general feature, available not only for sqlplus scripts — as mentioned, a series of 100 INSERTs can have 5 failing ones and commit statement will result in 95 new records.. So, in order to get similar behavior using current PostgreSQL features, one will have to: 1) use savepoints 2) use single-statement transactions; 3) (crazy stuff) use triggers on a fake relation to “test” inserts into the real one and ignore filing ones; 4) do pre-processing in some external tool, like script or pgloader; 5) more options? All these options will give significant performance and/or scripting penalty, while, say ORACLE, does it “for free” — I don't know the mechanics behind this feature, so looks like “free as in free speech” to me. The original OP's post is a feature request, IMHO. I'm not sure whether it is a good thing to have in general, but I really had to use it my scripts several times. -- Victor Y. Yegorov -- 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 list all schema names inside a PostgreSQL database through SQL
2012/11/15 Xiaobo Gu guxiaobo1...@gmail.com: How can I list all schema names inside a PostgreSQL database through SQL, especially thoese without any objects created inside it. Something like this: select n.nspname, count(o.oid) from pg_namespace n left join pg_class o on n.oid=o.relnamespace group by 1 order by count(o.oid)0, 1; I prefer to query PostgreSQL catalogs. You can obtain the same information using information_schema queries. -- Victor Y. Yegorov -- 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] obtain the difference between successive rows
2012/10/20 Berend Tober bto...@broadstripe.net: Your suggestion almost worked as is for this, except that you have to note that reading for meter #2 and meter #3 overlap (I briefly owned two houses), and that seemed to confuse the lag() function: SELECT electric_meter_pk, lag(reading_date) OVER(ORDER BY reading_date) as prev_date, reading_date, lag(meter_reading) OVER(ORDER BY reading_date) AS prev_reading, meter_reading, meter_reading - lag(meter_reading) OVER(ORDER BY reading_date) AS kWh_diff, reading_date - lag(reading_date) OVER(ORDER BY reading_date) as num_service_days FROM electric order by 1,3; 2 | 2009-04-09 | 2009-05-11 | 145595 | 146774 | 1179 |32 2 | 2009-05-11 | 2009-06-10 | 146774 | 148139 | 1365 |30 2 | 2009-06-26 | 2009-07-14 | 68502 | 149808 | 81306 |18 2 | 2009-07-14 | 2009-08-12 | 149808 | 151584 | 1776 |29 2 | 2009-09-12 | 2009-09-14 | 70934 | 152941 | 82007 | 2 3 | 2009-06-10 | 2009-06-26 | 148139 | 68502 |-79637 |16 3 | 2009-08-12 | 2009-08-13 | 151584 | 69738 |-81846 | 1 3 | 2009-08-13 | 2009-09-12 | 69738 | 70934 | 1196 |30 3 | 2009-09-14 | 2009-10-14 | 152941 | 71918 |-81023 |30 3 | 2009-10-14 | 2009-11-11 | 71918 | 72952 | 1034 |28 You can do … OVER(PARTITION BY electric_meter_pk ORDER BY reading_date) to split you data by meter. -- Victor Y. Yegorov -- 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] CTE materializing sets?
2012/10/9 Serge Fonville serge.fonvi...@gmail.com: This indeed is a very interesting question. At http://wiki.postgresql.org/wiki/CTEReadme it seems to suggest that a CTE is just rewritten and the resulting query is executed. As was mentioned a couple of times in this list, CTE do have optimization fence feature (per SQL Standard). I asked on the #postgresql channel and was pointed, that typically you get benefits of this feature when you have to join grouping subquery to itself. I went and did some tests. Table attempt contains e-mail delivery attempts for the postfix: # select relname,relpages,reltuples::numeric,pg_size_pretty(pg_relation_size(oid)) from pg_class where relname='attempt'; relname | relpages | reltuples | pg_size_pretty -+--+---+ attempt | 145117 | 4252530 | 1134 MB My default work_mem is 1MB on this instance. First, plain query with 2 subqueries: # explain (analyze, buffers) select a.eid, b.eid from (select recipient_email_id eid, count(*) cnt, min(tstamp) as minmsg, max(tstamp) as maxmsg from attempt group by recipient_email_id) a, (select recipient_email_id eid, count(*) cnt, min(tstamp) as minmsg, max(tstamp) as maxmsg from attempt group by recipient_email_id) b where a.minmsg = b.maxmsg; QUERY PLAN --- Merge Join (cost=1861911.11..1953183.16 rows=6067386 width=16) (actual time=65758.378..66115.400 rows=59845 loops=1) Merge Cond: (a.minmsg = b.maxmsg) Buffers: shared hit=1590 read=288644, temp read=103129 written=103134 - Sort (cost=930955.56..931042.64 rows=34835 width=16) (actual time=30242.503..30370.379 rows=212434 loops=1) Sort Key: a.minmsg Sort Method: external merge Disk: 5400kB Buffers: shared hit=779 read=144338, temp read=51481 written=51481 - Subquery Scan on a (cost=873875.76..927729.06 rows=34835 width=16) (actual time=26744.434..30008.996 rows=212434 loops=1) Buffers: shared hit=779 read=144338, temp read=50561 written=50561 - GroupAggregate (cost=873875.76..927380.71 rows=34835 width=16) (actual time=26744.433..29951.390 rows=212434 loops=1) Buffers: shared hit=779 read=144338, temp read=50561 written=50561 - Sort (cost=873875.76..884507.08 rows=4252528 width=16) (actual time=26744.273..28296.850 rows=4255749 loops=1) Sort Key: public.attempt.recipient_email_id Sort Method: external merge Disk: 108168kB Buffers: shared hit=779 read=144338, temp read=50561 written=50561 - Seq Scan on attempt (cost=0.00..187642.28 rows=4252528 width=16) (actual time=0.010..13618.612 rows=4255749 loops=1) Buffers: shared hit=779 read=144338 - Materialize (cost=930955.56..931129.73 rows=34835 width=16) (actual time=35515.860..35640.974 rows=214271 loops=1) Buffers: shared hit=811 read=144306, temp read=51648 written=51653 - Sort (cost=930955.56..931042.64 rows=34835 width=16) (actual time=35515.853..35586.598 rows=210800 loops=1) Sort Key: b.maxmsg Sort Method: external merge Disk: 5384kB Buffers: shared hit=811 read=144306, temp read=51648 written=51653 - Subquery Scan on b (cost=873875.76..927729.06 rows=34835 width=16) (actual time=31879.743..35251.218 rows=212434 loops=1) Buffers: shared hit=811 read=144306, temp read=50561 written=50561 - GroupAggregate (cost=873875.76..927380.71 rows=34835 width=16) (actual time=31879.741..35184.965 rows=212434 loops=1) Buffers: shared hit=811 read=144306, temp read=50561 written=50561 - Sort (cost=873875.76..884507.08 rows=4252528 width=16) (actual time=31879.577..33460.975 rows=4255749 loops=1) Sort Key: public.attempt.recipient_email_id Sort Method: external merge Disk: 108168kB Buffers: shared hit=811 read=144306, temp read=50561 written=50561 - Seq Scan on attempt (cost=0.00..187642.28 rows=4252528 width=16) (actual time=0.012..17637.516 rows=4255749 loops=1) Buffers: shared hit=811 read=144306 Total runtime: 67611.657 ms (34 rows) The source relation is scanned twice. Now, using CTE and it's materialization feature: # explain (analyze, buffers) with msgs as (select recipient_email_id eid, count(*) cnt, min(tstamp) as minmsg, max(tstamp) as maxmsg from attempt group by recipient_email_id) select a.eid, b.eid from msgs a, msgs b where a.minmsg=b.maxmsg;
[GENERAL] pg_upgrade default ports in the --help output
Greetings. I just noticed the following default ports in the pg_upgrade --help: -p, --old-port=OLDPORTold cluster port number (default 50432) -P, --new-port=NEWPORTnew cluster port number (default 50432) Why is this different from: --with-pgport=PORTNUM set default port number [5432] -- Victor Y. Yegorov -- 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_upgrade default ports in the --help output
2012/10/4 Adrian Klaver adrian.kla...@gmail.com: http://www.postgresql.org/docs/9.2/static/pgupgrade.html Obviously, no one should be accessing the clusters during the upgrade. pg_upgrade defaults to running servers on port 50432 to avoid unintended client connections. You can use the same port number for both clusters when doing an upgrade because the old and new clusters will not be running at the same time. However, when checking an old running server, the old and new port numbers must be different. Should have checked the docs first, sorry for the noise. Why is this different from: --with-pgport=PORTNUM set default port number [5432] Where is this coming from? ./configure --help -- Victor Y. Yegorov -- 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] Data recovery after inadvertent update?
Check this post on depesz.com: http://www.depesz.com/2012/04/04/lets-talk-dirty/ 2012/9/18 Craig Ringer ring...@ringerc.id.au Also, are there any functions to read raw tuple bytes to `RECORD's? I couldn't find any in `pageinspect', `pgstattuple', `adminpack', etc. Am I right in guessing that they're pretty much going to require hand data recovery or the use of some custom C extension code to get at the data - if it still exists? -- Victor Y. Yegorov
Re: [GENERAL] return text from explain
Hope this helps:
CREATE OR REPLACE FUNCTION explain(in_sql text) RETURNS TABLE(explain_line
text) AS $explain$
BEGIN
RETURN QUERY EXECUTE 'EXPLAIN '||in_sql;
END;
$explain$ LANGUAGE plpgsql;
SELECT * FROM explain('SELECT * FROM pg_locks');
--
Victor Y. Yegorov
[GENERAL] Extensions and roles for access administration
Greetings. I've developed a small extension, that is essentially a collection of tables with a bunch of PL/pgSQL functions, that are API for the whole thing. Inside the extension script I am creating extra roles, and access to the extension's functions is provided using these extra roles. Given extension is named 'dmf' and one of the roles is 'dmf_user', administrator has to grant 'dmf_user' to any user that is eligible to use the provided APIs. My questions are: - is this the correct approach to include into extension a set of administrative roles? - I've noticed, that after I DROP EXTENSION, all roles are still there in the database. Is this expected? -- Victor Y. Yegorov
Re: [GENERAL] Extensions and roles for access administration
Thanks, this clarifies things for me. There's DROP ROLE IF EXISTS, which I'm using. 2012/6/22 Tom Lane t...@sss.pgh.pa.us Roles are not considered to be part of an extension: they really can't be, since an extension is local to a database while a role is global to the whole installation. As per the documentation, you're allowed to do CREATE ROLE in an extension script but it won't be managed by the extension mechanism. Not sure about a better solution to your problem offhand. It might be sane for the script to do CREATE ROLE IF NOT EXISTS, if we had that, which we do not :-( -- Victor Y. Yegorov
[GENERAL] How to find compiled-in default port number?
Greetings. Is there a way to find out the compiled-in port number? I can parse `pg_config` output to check out port in cases port was actually specified. However if defaults had been used, is there any tool that will tell me the magic 5432 number or should I silently stick to this number in my scripts? Thanks in advance! -- Victor Y. Yegorov
