Re: [HACKERS] proposal: lob conversion functionality

2013-08-13 Thread Pavel Stehule
Hello 2013/8/12 Pavel Stehule pavel.steh...@gmail.com: 2013/8/10 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I found so there are no simple API for working with LO from PL without access to file system. What? See lo_open(), loread(), lowrite(), etc. so

Re: [HACKERS] Modyfication Sort Merge Join Alghoritm

2013-08-13 Thread David Fetter
On Mon, Aug 12, 2013 at 08:16:59PM -0700, Jeff Janes wrote: On Mon, Aug 12, 2013 at 1:31 PM, tubadzin tubad...@o2.pl wrote: Hi users. I want to sure, that is no this implementation in Postgresql: For Sorte Merge Join Alghoritm: If the large input arrives sorted, g-join joins its pages

Re: [HACKERS] updatable/deletable terminology

2013-08-13 Thread Dean Rasheed
On 13 August 2013 00:01, Peter Eisentraut pete...@gmx.net wrote: On Wed, 2013-08-07 at 21:19 -0400, Peter Eisentraut wrote: To make the view updatable, provide an unconditional ON DELETE DO INSTEAD rule or an INSTEAD OF DELETE trigger. I think it's a bit strange to claim that adding a DELETE

Re: [HACKERS] 9.3 release notes suggestions

2013-08-13 Thread Etsuro Fujita
Thanks for the many suggestions on improving the 9.3 release notes. There were many ideas I would have never thought of. Please keep the suggestions coming. One small suggestion: listitem para Allow link linkend=SQL-CREATEFOREIGNDATAWRAPPERforeign data

[HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Tomonari Katsumata
Hi, Could anyone tell me how to create read-only view on PostgreSQL 9.3 ? I've been testing updatable views and noticed that all simple views are updatable. When I use pg_dump for upgrading from PostgreSQL 9.2 to PostgreSQL 9.3 and if the databse has views, all views are updatable on the

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Szymon Guz
On 13 August 2013 11:43, Tomonari Katsumata katsumata.tomon...@po.ntts.co.jp wrote: Hi, Could anyone tell me how to create read-only view on PostgreSQL 9.3 ? I've been testing updatable views and noticed that all simple views are updatable. When I use pg_dump for upgrading from

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Tomonari Katsumata
Hi Szymon, Thank you for response. Could you show an example? I do below things on one server. The path to database cluster and port are different with each other. [9.2.4] initdb --no-locale -E UTF8 pg_ctl start createdb testdb psql testdb -c create table tbl(i int) psql testdb -c insert

Re: [HACKERS] Regarding BGworkers

2013-08-13 Thread Robert Haas
On Mon, Aug 5, 2013 at 9:20 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Aug 2, 2013 at 1:40 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: That seems more mess than just keeping that function in postmaster.c. I agree with moving the other one. Please find attached a

Re: [HACKERS] timeline signedness

2013-08-13 Thread Peter Eisentraut
On Wed, 2013-08-07 at 21:55 -0400, Peter Eisentraut wrote: WAL timelines are unsigned 32-bit integers everywhere, except the replication parser (replication/repl_gram.y and replication/repl_scanner.l) treats them as signed 32-bit integers. It's obviously a corner case, but it would be prudent

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Merlin Moncure
On Tue, Aug 13, 2013 at 5:37 AM, Tomonari Katsumata katsumata.tomon...@po.ntts.co.jp wrote: Hi Szymon, Thank you for response. Could you show an example? I do below things on one server. The path to database cluster and port are different with each other. [9.2.4] initdb --no-locale -E

[HACKERS] psql --single-transaction does not work as expected

2013-08-13 Thread Rafael Martinez
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello I want to report that psql --single-transaction does not work as one can expect after reading the help information for psql. psql --help says: - -1 (one), --single-transaction: execute command file as a single transaction If you run psql -1

Re: [HACKERS] psql --single-transaction does not work as expected

2013-08-13 Thread Bruce Momjian
On Tue, Aug 13, 2013 at 04:04:50PM +0200, Rafael Martinez wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello I want to report that psql --single-transaction does not work as one can expect after reading the help information for psql. psql --help says: - -1 (one),

Re: [HACKERS] Foreground vacuum and buffer access strategy

2013-08-13 Thread Robert Haas
On Mon, Aug 12, 2013 at 11:47 PM, Jeff Janes jeff.ja...@gmail.com wrote: Reviving a very old thread, because I've run into the issue again. On Tue, May 29, 2012 at 11:58 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, May 25, 2012 at 4:06 PM, Jeff Janes jeff.ja...@gmail.com wrote: If I

Re: [HACKERS] Regarding BGworkers

2013-08-13 Thread Alvaro Herrera
Robert Haas escribió: On Mon, Aug 5, 2013 at 9:20 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Fri, Aug 2, 2013 at 1:40 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: That seems more mess than just keeping that function in postmaster.c. I agree with moving the other one.

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Hannu Krosing
On 08/13/2013 03:25 PM, Merlin Moncure wrote: On Tue, Aug 13, 2013 at 5:37 AM, Tomonari Katsumata katsumata.tomon...@po.ntts.co.jp wrote: Hi Szymon, Thank you for response. Could you show an example? I do below things on one server. The path to database cluster and port are different

Re: [HACKERS] pg_dump and schema names

2013-08-13 Thread Bruce Momjian
On Fri, Aug 9, 2013 at 02:15:31PM -0400, Bruce Momjian wrote: Well, it's certainly not immediately obvious why we shouldn't merge them. But I would have expected the function's header comment to now explain that the output is intentionally not schema-qualified and assumes that the search

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Merlin Moncure
On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing ha...@2ndquadrant.com wrote: On 08/13/2013 03:25 PM, Merlin Moncure wrote: I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by relying on the read-only-ness you were relying

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Andrew Dunstan
On 08/13/2013 12:09 PM, Merlin Moncure wrote: On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing ha...@2ndquadrant.com wrote: On 08/13/2013 03:25 PM, Merlin Moncure wrote: I chatted about this on IRC for a bit. Apparently, updatability of views is a mandatory feature in the sql standard and by

Re: [HACKERS] 9.3 release notes suggestions

2013-08-13 Thread 'Bruce Momjian'
On Tue, Aug 13, 2013 at 05:59:05PM +0900, Etsuro Fujita wrote: Thanks for the many suggestions on improving the 9.3 release notes. There were many ideas I would have never thought of. Please keep the suggestions coming. One small suggestion: listitem para Allow

Re: [HACKERS] danger of stats_temp_directory = /dev/shm

2013-08-13 Thread Jeff Janes
On Thu, Apr 25, 2013 at 8:24 AM, Peter Eisentraut pete...@gmx.net wrote: On 4/25/13 12:09 AM, Tom Lane wrote: I think we need it fixed to reject any stats_temp_directory that is not postgres-owned with restrictive permissions. The problem here is not with what it deletes, it's with the

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Josh Berkus
All, In any case, using permissions is a somewhat leaky bandaid, since superusers have overriding access privileges anyway. A better way to do what the OP wants might be to have a view trigger that raises an exception. I think it would be better to supply a script which revoked write

Re: [HACKERS] danger of stats_temp_directory = /dev/shm

2013-08-13 Thread Josh Berkus
On 08/13/2013 09:57 AM, Jeff Janes wrote: Is this a blocker for 9.3? Why would it be? This issue doesn't originate with 9.3. If it is a concern of not what is deleted but rather that someone can inject a poisoned stats file into the directory, does it need to be back-patched all the way, as

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Hannu Krosing
On 08/13/2013 06:23 PM, Andrew Dunstan wrote: On 08/13/2013 12:09 PM, Merlin Moncure wrote: On Tue, Aug 13, 2013 at 10:12 AM, Hannu Krosing ha...@2ndquadrant.com wrote: On 08/13/2013 03:25 PM, Merlin Moncure wrote: I chatted about this on IRC for a bit. Apparently, updatability of views is

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Stephen Frost
* Hannu Krosing (ha...@2ndquadrant.com) wrote: If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying table. You also probably did not GRANT only SELECT to your views as this was the

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Tom Lane
Hannu Krosing ha...@2ndquadrant.com writes: If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying table. Unless you'd explicitly granted those users insert/update/delete privilege on the

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Andrew Dunstan
On 08/13/2013 01:33 PM, Hannu Krosing wrote: In any case, using permissions is a somewhat leaky bandaid, since superusers have overriding access privileges anyway. A better way to do what the OP wants might be to have a view trigger that raises an exception. Superuser can easily disable or

[HACKERS] Release schedule for PG 9.3

2013-08-13 Thread Tom Lane
It seems that the volume of 9.3-specific bug reports is tailing off. After some discussion, the core committee has agreed to produce a 9.3rc1 version next week (that is, wrap Monday the 19th for public announcement Thursday the 22nd). If no showstopper bugs are reported in the next couple of

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Merlin Moncure
On Tue, Aug 13, 2013 at 1:18 PM, Tom Lane t...@sss.pgh.pa.us wrote: There's no security hole here; if someone can do something that they couldn't do before, it's because you explicitly granted them privileges to do so. This point is completely bogus. Very, very few applications I've run

Re: [HACKERS] Review: UNNEST (and other functions) WITH ORDINALITY

2013-08-13 Thread Robert Haas
On Tue, Aug 6, 2013 at 6:10 PM, Greg Stark st...@mit.edu wrote: The only other case I could come up with in my regression tests is pretty esoteric: CREATE COLLATION nulls (locale='C'); ALTER OPERATOR CLASS text_ops USING btree RENAME TO first; CREATE TABLE nulls_first(t text); CREATE INDEX

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread David Fetter
On Tue, Aug 13, 2013 at 10:24:32AM -0700, Josh Berkus wrote: All, In any case, using permissions is a somewhat leaky bandaid, since superusers have overriding access privileges anyway. A better way to do what the OP wants might be to have a view trigger that raises an exception. I

Re: [HACKERS] Foreground vacuum and buffer access strategy

2013-08-13 Thread Greg Stark
On Tue, Aug 13, 2013 at 3:45 PM, Robert Haas robertmh...@gmail.com wrote: I'm not sure what the right thing to do here is, but I definitely agree there's a problem. There are definitely cases where people want or indeed need to vacuum as fast as possible, and using a small ring buffer is not

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Josh Berkus
On 08/13/2013 11:18 AM, Tom Lane wrote: Hannu Krosing ha...@2ndquadrant.com writes: If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying table. Unless you'd explicitly granted those

Re: [HACKERS] Review: UNNEST (and other functions) WITH ORDINALITY

2013-08-13 Thread Greg Stark
On Tue, Aug 13, 2013 at 8:20 PM, Robert Haas robertmh...@gmail.com wrote: Blech. Well, that's why we need to stop hacking the lexer before we shoot a hole through our foot that's too large to ignore. But it's not this patch's job to fix that problem. Hm. I thought it was. However it turns

[HACKERS] TODO request: multi-dimensional arrays in PL/pythonU

2013-08-13 Thread Josh Berkus
All, Currently PL/python has 1 dimension hardcoded for returning arrays: create or replace function nparr () returns float[][] language plpythonu as $f$ from numpy import array x = ((1.0,2.0),(3.0,4.0),(5.0,6.0),) return x $f$; josh=# select nparr() ; ERROR: invalid input syntax for type

Re: [HACKERS] [GENERAL] Possible bug with row_to_json

2013-08-13 Thread Tom Lane
I wrote: Jack Christensen j...@jackchristensen.com writes: It ignored the rename. I looked into this and found that the culprit is the optimization that skips ExecProject() if a scan plan node is not doing any useful projection. Further poking at this issue shows that there are related

Re: [HACKERS] [GENERAL] Possible bug with row_to_json

2013-08-13 Thread Merlin Moncure
On Tue, Aug 13, 2013 at 4:34 PM, Tom Lane t...@sss.pgh.pa.us wrote: Since this behavior can also be demonstrated in 9.2 (and maybe further back using xml features?), I don't think we should consider it a blocker bug for 9.3. I'm planning to set it on the back burner for the moment and go

Re: [HACKERS] Regarding BGworkers

2013-08-13 Thread Michael Paquier
On Tue, Aug 13, 2013 at 11:59 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: maybe_start_bgworker() in postmaster.c do_start_bgworker() in postmaster.c StartBackgroundWorker() in bgworker.c This formulation is fine, thanks. Instead of maybe_start_bgworker, what

Re: [HACKERS] UNNEST with multiple args, and TABLE with multiple funcs

2013-08-13 Thread Josh Berkus
On 08/13/2013 06:54 AM, Andrew Gierth wrote: Summary: This patch implements a method for expanding multiple SRFs in parallel that does not have the surprising LCM behaviour of SRFs-in-select-list. (Functions returning fewer rows are padded with nulls instead.) BTW, if anyone is unsure of

Re: [HACKERS] UNNEST with multiple args, and TABLE with multiple funcs

2013-08-13 Thread Craig Ringer
On 08/14/2013 08:22 AM, Josh Berkus wrote: On 08/13/2013 06:54 AM, Andrew Gierth wrote: Summary: This patch implements a method for expanding multiple SRFs in parallel that does not have the surprising LCM behaviour of SRFs-in-select-list. (Functions returning fewer rows are padded with

Re: [HACKERS] Regarding BGworkers

2013-08-13 Thread Robert Haas
On Tue, Aug 13, 2013 at 8:07 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Tue, Aug 13, 2013 at 11:59 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: maybe_start_bgworker() in postmaster.c do_start_bgworker() in postmaster.c StartBackgroundWorker() in

CREATE TRANSFORM syntax (was Re: [HACKERS] [PATCH] Add transforms feature)

2013-08-13 Thread Peter Eisentraut
On Mon, 2013-07-08 at 23:00 -0700, Hitoshi Harada wrote: On Sun, Jul 7, 2013 at 12:06 PM, Peter Eisentraut pete...@gmx.net wrote: On Thu, 2013-07-04 at 02:18 -0700, Hitoshi Harada wrote: as someone suggested in the previous thread, it might be a variant of CAST. CREATE CAST (hstore AS

Re: [HACKERS] How to create read-only view on 9.3

2013-08-13 Thread Tomonari Katsumata
Hi, (2013/08/14 5:24), Josh Berkus wrote: On 08/13/2013 11:18 AM, Tom Lane wrote: Hannu Krosing ha...@2ndquadrant.com writes: If you earlier used views for granting limited read access to some views you definitely did not want view users suddenly gain also write access to underlying

Re: [HACKERS] danger of stats_temp_directory = /dev/shm

2013-08-13 Thread Jeff Janes
On Tuesday, August 13, 2013, Josh Berkus wrote: On 08/13/2013 09:57 AM, Jeff Janes wrote: Is this a blocker for 9.3? Why would it be? This issue doesn't originate with 9.3. Before 9.3, it would delete one specific file from a potentially shared directory. In 9.3 it deletes the entire

Re: [HACKERS] Foreground vacuum and buffer access strategy

2013-08-13 Thread Amit Kapila
On Wed, Aug 14, 2013 at 1:41 AM, Greg Stark st...@mit.edu wrote: On Tue, Aug 13, 2013 at 3:45 PM, Robert Haas robertmh...@gmail.com wrote: I'm not sure what the right thing to do here is, but I definitely agree there's a problem. There are definitely cases where people want or indeed need to

[HACKERS] pgstat_reset_remove_files ignores its argument

2013-08-13 Thread Jeff Janes
in 9.3 and 9.4, pgstat_reset_remove_files uses the global variable pgstat_stat_directory rather than the argument it is passed, directory. On crash recovery, this means the tmp directory gets cleared twice and the permanent pg_stat doesn't get cleared at all. It seems like the obvious one line

Re: [HACKERS] System catalog vacuum issues

2013-08-13 Thread Vlad Arkhipov
I used to use VACUUM FULL periodically to resolve the issue, but the problem arises again in 2-3 months. Here is the statistics (from pgstattuple). I run VACUUM FULL on 2013-08-07. dcdb=# select date, relpages, reltuples, table_len, tuple_count, tuple_percent, dead_tuple_count, dead_tuple_len,