Re: [HACKERS] data to json enhancements
On 09/28/2012 12:42 AM, Andrew Dunstan wrote: On 09/27/2012 06:58 PM, Hannu Krosing wrote: On 09/27/2012 09:18 PM, Andrew Dunstan wrote: On 09/27/2012 10:36 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 09/27/2012 09:22 AM, Robert Haas wrote: Maybe I am being too pedantic about this and there is a way to make it all work nicely, but it sure feels like using the casting machinery here is blending together two different concepts that are only sometimes the same. OK. I think that's a very good point. I guess I was kinda swept away by this being suggested by a couple of influential people. Well, that doesn't make it wrong, it just means there's more work needed. I'm not that thrilled with magic assumptions about function names either; schema search path issues, for example, will make that dangerous. We've gone to considerable lengths to avoid embedding assumptions about operator names, and assumptions about function names aren't any better. There are at least three ways we could use the cast machinery for this: (1) Reject Robert's assumption that we have to support both interpretations for every cast situation. For instance, it doesn't seem that unreasonable to me to insist that you have to cast to text and then to json if you want the literal-reinterpretation behavior. Maybe cast not to text but to cstring for getting the text-is-already-json ? That is, reuse the current type io as literal casts. This way a cast of '{a: 1}'::json::text will fail, as this json value really does not represent a text/string value. The main problem then is figuring out a convenient way to provide interpretation #2 for text itself. The trouble is, ISTM, that both things seem equally intuitive. You could easily argue that x::text::json means take x as text and treat it as json, or that it means take x as text and produce a valid json value from it by escaping and quoting it. It's particularly ambiguous when x is itself already a text value. If we go this way I suspect we'll violate POLA for a good number of users. It may be easier to sort this out if we think in terms of symmetry and unambiguity. let's postulate that mytype::json::mytype and json::mytype::json should always reproduce the original result or they should fail. Where are all these casts from json going to come from? What is going to dequote and unescape strings, or turn objects into hstores? as json is defined to encode only 3 base types - boolean (true/false), number and string - and two composite types - array and object - it should not be too hard to provide casts for these and then use existing casts to go on from number and text Something extra should probably be done for number, perhaps we need separate casts for float and decimal/numeric but the rest should be relatively simple. the json null vs SQL NULL poses and interesting problem though ;) You're making this much bigger than what I had in mind. The advantage of Tom's option (3) that I liked is that it is very minimal. Any type can provide its own function for conversion to json. If it's there we use it, if it's not we use its standard text representation. Let's stick to the KISS principle. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] data to json enhancements
On 9/27/12 10:36 AM, Tom Lane wrote: (1) Reject Robert's assumption that we have to support both interpretations for every cast situation. For instance, it doesn't seem that unreasonable to me to insist that you have to cast to text and then to json if you want the literal-reinterpretation behavior. The main problem then is figuring out a convenient way to provide interpretation #2 for text itself. I don't think it's going to work to special-case text like that. For one thing, it would mean wildly different things would happen depending on whether text or varchar is used. (2) Add another hidden argument to cast functions, or perhaps repurpose one of the ones that exist now. This is likely to come out rather ugly because of the need to shoehorn it into an API that's already suffered a couple of rounds of after-the-fact additions, but it's certainly possible in principle. Could there be a case where you'd want to be able to serialize a type to JSON like that, but don't actually want a regular cast to exist (even explicit)? The main thing I'd want is to not define it in a JSON-only fashion --- so the first thing is to be able to explain the distinction we're trying to make in a type-independent way. There is XML, which would use the same mechanism. For example, we currently have a cast from text to xml, which interprets the text as an XML document. The other interpretation would escape , , etc. (3) Invent an auxiliary type along the lines of json_value and say that you create a cast from foo to json_value when you want one interpretation, or directly to json if you want the other. Then things like record_to_json would look for the appropriate type of cast. This is a bit ugly because the auxiliary type has no reason to live other than to separate the two kinds of cast, but it avoids creating any new JSON-specific mechanisms in the type system. What if some wanted to create a yaml type as an extension. How would they associate yaml and yaml_value? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] data to json enhancements
On 09/28/2012 08:22 AM, Peter Eisentraut wrote: (3) Invent an auxiliary type along the lines of json_value and say that you create a cast from foo to json_value when you want one interpretation, or directly to json if you want the other. Then things like record_to_json would look for the appropriate type of cast. This is a bit ugly because the auxiliary type has no reason to live other than to separate the two kinds of cast, but it avoids creating any new JSON-specific mechanisms in the type system. What if some wanted to create a yaml type as an extension. How would they associate yaml and yaml_value? Well, of course it wouldn't be a problem if the code could know the OID of yaml_value ... :-) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Switching timeline over streaming replication
On Tuesday, September 25, 2012 6:29 PM Heikki Linnakangas wrote: On 25.09.2012 10:08, Heikki Linnakangas wrote: On 24.09.2012 16:33, Amit Kapila wrote: In any case, it will be better if you can split it into multiple patches: 1. Having new functionality of Switching timeline over streaming replication 2. Refactoring related changes. Ok, here you go. xlog-c-split-1.patch contains the refactoring of existing code, with no user-visible changes. streaming-tli-switch-2.patch applies over xlog-c-split-1.patch, and contains the new functionality. Please find the initial review of the patch. Still more review is pending, but I thought whatever is done I shall post Basic stuff: -- - Patch applies OK - Compiles cleanly with no warnings - Regression tests pass. - Documentation changes are mostly fine. - Basic replication tests works. Testing - Start primary server Start standby server Start cascade standby server Stopped the primary server Promoted the standby server with ./pg_ctl -D data_repl promote In postgresql.conf file archive_mode = off The following logs are observing in the cascade standby server. LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: walreceiver ended streaming and awaits new instructions LOG: record with zero length at 0/17E3888 LOG: re-handshaking at position 0/100 on tli 1 LOG: fetching timeline history file for timeline 2 from primary server LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: walreceiver ended streaming and awaits new instructions LOG: re-handshaking at position 0/100 on tli 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 In postgresql.conf file archive_mode = on The following logs are observing in the cascade standby server. LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: walreceiver ended streaming and awaits new instructions sh: /home/amit/installation/bin/data_sub/pg_xlog/archive_status/0001 0002: No such file or directory LOG: record with zero length at 0/20144B8 sh: /home/amit/installation/bin/data_sub/pg_xlog/archive_status/0001 0002: No such file or directory LOG: re-handshaking at position 0/200 on tli 1 LOG: fetching timeline history file for timeline 2 from primary server LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: walreceiver ended streaming and awaits new instructions sh: /home/amit/installation/bin/data_sub/pg_xlog/archive_status/0001 0002: No such file or directory sh: /home/amit/installation/bin/data_sub/pg_xlog/archive_status/0001 0002: No such file or directory LOG: re-handshaking at position 0/200 on tli 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: walreceiver ended streaming and awaits new instructions Verified that files are present in respective directories. Code Review 1. In function readTimeLineHistory(), two mechanisms are used to fetch timeline from history file +sscanf(fline, %u\t%X/%X, tli, switchpoint_hi, switchpoint_lo); + +/* expect a numeric timeline ID as first field of line */ +tli = (TimeLineID) strtoul(ptr, endptr, 0); If we use new mechanism, it will not be able to detect error as it is doing in current case. 2. In function readTimeLineHistory(), +fd = AllocateFile(path, r); +if (fd == NULL) +{ +if (errno != ENOENT) +ereport(FATAL, +(errcode_for_file_access(), + errmsg(could not open file \%s\: %m, path))); +/* Not there, so assume no parents */ +return list_make1_int((int) targetTLI); +} still return list_make1_int((int) targetTLI); is used. 3. Function timelineOfPointInHistory(), should return the timeline of recptr passed to it. a. is it okay to decide based on xlog recordpointer that which timeline it belongs to, as different timelines can have same xlog recordpointer? b. it seems from logic that it will return timeline previous to the timeline of recptr passed. For example if the timeline 3's switchpoint is equal to recptr passed then it will return timeline 2. 4. In writeTimeLineHistory function variable endTLI is never used. 5. In header of function writeTimeLineHistory(), can give explanation about XLogRecPtr switchpoint 6. @@ -6869,11 +5947,35 @@ StartupXLOG(void) */ if (InArchiveRecovery) { +charreason[200]; + +/* + * Write comment
Re: [HACKERS] [9.1] 2 bugs with extensions
On Wed, Sep 26, 2012 at 7:15 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Marko Kreen mark...@gmail.com writes: Can we work out a minimal example to reproduce the bug? Yes, the above text or sql/pgq_coop/sql/pgq_coop_init_ext.sql I guess you could replace pgq_coop with any extension just consisting of just functions. I did just that, with a single function, and couldn't reproduce the problem either in 9.1 nor in master, with relocatable = true then with relocatable = false and schema = 'pg_catalog' as in your repository. Indeed, after another makefile reorg, I could not reproduce it on skytools master either, some digging showed that due to a makefile bug ($ instead $^) the ADD SCHEMA was missing from .sql file. So no bug in postgres. (The Makefile in skytools/sql/pgq_coop fails on my OS) How does it fail? Are you using gnu make? What version? I guess sed is the problem here, it's a BSD variant: Could you test if Skytools git now works for you? I replaced sed usage with awk there, perhaps that will be more portable. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] setting per-database/role parameters checks them against wrong context
Peter Eisentraut pete...@gmx.net writes: Example: create temporary table foo (a int); insert into foo values (1); alter role peter set temp_buffers = 120; ERROR: 22023: invalid value for parameter temp_buffers: 120 DETAIL: temp_buffers cannot be changed after any temporary tables have been accessed in the session. Another example: set log_statement_stats = on; alter role peter set log_parser_stats = on; ERROR: 22023: invalid value for parameter log_parser_stats: 1 DETAIL: Cannot enable parameter when log_statement_stats is true. Another example is that in =9.1, ALTER DATABASE ... SET search_path would check the existence of the schema in the current database, but that was done away with in 9.2. The first example could probably be fixed if check_temp_buffers() paid attention to the GucSource, but in the second case and in general there doesn't seem to be a way to distinguish assigning per-database setting and enacting per-database setting as a source. Ideas? Perhaps instead of trying to solve the problem as stated, it would be more useful to put the effort into getting rid of context-sensitive restrictions on GUC settings. Neither of the examples above seem particularly essential - they are just protecting incomplete implementations. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 64-bit API for large object
Excerpts from Kohei KaiGai's message of jue sep 27 01:01:18 -0300 2012: * I have a question. What is the meaning of INT64_IS_BUSTED? It seems to me a marker to indicate a platform without 64bit support. However, the commit 901be0fad4034c9cf8a3588fd6cf2ece82e4b8ce says as follows: | Remove all the special-case code for INT64_IS_BUSTED, per decision that | we're not going to support that anymore. Yeah, I think we should just get rid of those bits. I don't remember seeing *any* complaint when INT64_IS_BUSTED was removed, which means nobody was using that code anyway. Now there is one more problem in this area which is that the patch defined a new type pg_int64 for frontend code (postgres_ext.h). This seems a bad idea to me. We already have int64 defined in c.h. Should we expose int64 to postgres_ext.h somehow? Should we use standard- mandated int64_t instead? One way would be to have a new configure check for int64_t, and if that type doesn't exist, then just don't provide the 64 bit functionality to frontend. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Doc patch, put pg_temp into the documentation's index
Hi, 2 patches: pg_temp-toindex.patch Puts pg_temp into the index of the docs. (Line lengths are ugly so the change can be easily reviewed.) pg_temp-reformat.patch Reformats the doc source after the above patch. (Fixes line length.) Regards, Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 4bd06ed..f041309 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -4799,7 +4799,7 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; Likewise, the current session's temporary-table schema, literalpg_temp_replaceablennn//, is always searched if it exists. It can be explicitly listed in the path by using the -alias literalpg_temp/. If it is not listed in the path then +alias literalpg_temp/indextermprimarypg_temp//. If it is not listed in the path then it is searched first (even before literalpg_catalog/). However, the temporary schema is only searched for relation (table, view, sequence, etc) and data type names. It is never searched for diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 4336e4b..fee6f53 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -684,7 +684,7 @@ SELECT * FROM dup(42); temporary-table schema, which is searched first by default, and is normally writable by anyone. A secure arrangement can be had by forcing the temporary schema to be searched last. To do this, -write literalpg_temp/ as the last entry in varnamesearch_path/. +write literalpg_temp/indextermprimarypg_temp/secondarysecuring functions// as the last entry in varnamesearch_path/. This function illustrates safe usage: /para diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index f041309..d5bd993 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -4797,13 +4797,15 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; para Likewise, the current session's temporary-table schema, -literalpg_temp_replaceablennn//, is always searched if it -exists. It can be explicitly listed in the path by using the -alias literalpg_temp/indextermprimarypg_temp//. If it is not listed in the path then -it is searched first (even before literalpg_catalog/). However, -the temporary schema is only searched for relation (table, view, -sequence, etc) and data type names. It is never searched for -function or operator names. +literalpg_temp_replaceablennn//, is always searched if +it exists. It can be explicitly listed in the path by using +the alias +literalpg_temp/indextermprimarypg_temp//. If it +is not listed in the path then it is searched first (even +before literalpg_catalog/). However, the temporary schema +is only searched for relation (table, view, sequence, etc) and +data type names. It is never searched for function or +operator names. /para para diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index fee6f53..8632a76 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -676,15 +676,16 @@ SELECT * FROM dup(42); para Because a literalSECURITY DEFINER/literal function is executed with the privileges of the user that created it, care is needed to -ensure that the function cannot be misused. For security, -xref linkend=guc-search-path should be set to exclude any schemas -writable by untrusted users. This prevents -malicious users from creating objects that mask objects used by the -function. Particularly important in this regard is the -temporary-table schema, which is searched first by default, and -is normally writable by anyone. A secure arrangement can be had -by forcing the temporary schema to be searched last. To do this, -write literalpg_temp/indextermprimarypg_temp/secondarysecuring functions// as the last entry in varnamesearch_path/. +ensure that the function cannot be misused. For security, xref +linkend=guc-search-path should be set to exclude any schemas +writable by untrusted users. This prevents malicious users from +creating objects that mask objects used by the function. +Particularly important in this regard is the temporary-table +schema, which is searched first by default, and is normally +writable by anyone. A secure arrangement can be had by forcing +the temporary schema to be searched last. To do this, write +literalpg_temp/indextermprimarypg_temp/secondarysecuring +functions// as the last entry in varnamesearch_path/. This function illustrates safe usage:
[HACKERS] Doc patch, index search_path where it's used to secure functions
Hi, Doc patch, index search_path where it's used to secure functions. search_path-securing.patch Against git head. (As are the previous doc patches.) Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 8632a76..fb8a42d 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -673,6 +673,11 @@ SELECT * FROM dup(42); refsect1 id=sql-createfunction-security titleWriting literalSECURITY DEFINER/literal Functions Safely/title + indexterm + primarysearch_path/ + secondarysecuring functions/ + /indexterm + para Because a literalSECURITY DEFINER/literal function is executed with the privileges of the user that created it, care is needed to -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Doc patch, index search_path where it's used to secure functions
On 09/28/2012 11:28:39 AM, Karl O. Pinc wrote: Doc patch, index search_path where it's used to secure functions. search_path-securing.patch Second version. Should be indexing the concept, not the run-time setting. Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein diff --git a/doc/src/sgml/ref/create_function.sgml b/doc/src/sgml/ref/create_function.sgml index 8632a76..fb8a42d 100644 --- a/doc/src/sgml/ref/create_function.sgml +++ b/doc/src/sgml/ref/create_function.sgml @@ -673,6 +673,11 @@ SELECT * FROM dup(42); refsect1 id=sql-createfunction-security titleWriting literalSECURITY DEFINER/literal Functions Safely/title + indexterm + primarysearch path/ + secondarysecuring functions/ + /indexterm + para Because a literalSECURITY DEFINER/literal function is executed with the privileges of the user that created it, care is needed to -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Doc patch, normalize search_path in index
Hi, The attached patch (against git head) normalizes search_path as the thing indexed and uses a secondary index term to distinguish the configuration parameter from the run-time setting. search path the concept remains distinguished in the index from search_path the setting/config param. It's hard to say whether it's useful to make this distinction. From a practical perspective it's easy for the eye to stop scanning when the indent level changes and so fail to notice that both search path and search_path are index entries. At least the index is a lot more tidy than before. Regards, Karl k...@meme.com Free Software: You don't pay back, you pay forward. -- Robert A. Heinlein diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index d5bd993..5671ddf 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -4758,7 +4758,8 @@ COPY postgres_log FROM '/full/path/to/logfile.csv' WITH csv; varlistentry id=guc-search-path xreflabel=search_path termvarnamesearch_path/varname (typestring/type)/term indexterm - primaryvarnamesearch_path/ configuration parameter/primary + primaryvarnamesearch_path//primary + secondaryconfiguration parameter/secondary /indexterm indextermprimarypath/secondaryfor schemas// listitem diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index d6e5d64..367dce7 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1736,6 +1736,7 @@ CREATE TABLE public.products ( ... ); indexterm primarysearch_path/primary +secondaryrun-time setting/secondary /indexterm para -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] XLogInsert scaling, revisited
On Fri, Sep 28, 2012 at 12:58 AM, Heikki Linnakangas hlinnakan...@vmware.com wrote: Hmm, I cannot reproduce this on my Linux laptop. However, I think I see what the problem is: the assertion should assert that (*CurrPos* % XLOG_BLCKZ = SizeOfXLogShortPHD), not currpos. The former is an XLogRecPtr, the latter is a pointer. If the WAL buffers are aligned at 8k boundaries, the effect is the same, but otherwise the assertion is just wrong. And as it happens, if O_DIRECT is defined, we align WAL buffers at XLOG_BLCKSZ. I think that's why I don't see this on my laptop. Does Mac OS X not define O_DIRECT? Yes, AFAIK Mac OS doesn't support O_DIRECT. Anyway, attached is a patch with that fixed. Thanks! In new patch, initdb was successfully completed. I encountered another strange issue: When I called pg_switch_xlog() while pgbench -j 1 -c 1 -T 600 is running, both pg_switch_xlog() and all connections of pgbench got stuck. Here is the backtrace of stuck pg_switch_xlog(): (gdb) bt #0 0x7fff8fe13c46 in semop () #1 0x000106b97d34 in PGSemaphoreLock () #2 0x000106a2e8cf in WaitXLogInsertionsToFinish () #3 0x000106a2fe8b in XLogInsert () #4 0x000106a30576 in RequestXLogSwitch () #5 0x000106a37950 in pg_switch_xlog () #6 0x000106b19bd3 in ExecMakeFunctionResult () #7 0x000106b14be1 in ExecProject () #8 0x000106b2b83d in ExecResult () #9 0x000106b14000 in ExecProcNode () #10 0x000106b13080 in standard_ExecutorRun () #11 0x000106be919f in PortalRunSelect () #12 0x000106bea5c9 in PortalRun () #13 0x000106be8519 in PostgresMain () #14 0x000106ba4ef9 in PostmasterMain () #15 0x000106b418f1 in main () Here is the backtrace of stuck pgbench connection: (gdb) bt #0 0x7fff8fe13c46 in semop () #1 0x000106b97d34 in PGSemaphoreLock () #2 0x000106bda95e in LWLockAcquireWithCondVal () #3 0x000106a25556 in WALInsertLockAcquire () #4 0x000106a2fa8a in XLogInsert () #5 0x000106a0386d in heap_update () #6 0x000106b2a03e in ExecModifyTable () #7 0x000106b14010 in ExecProcNode () #8 0x000106b13080 in standard_ExecutorRun () #9 0x000106be9ceb in ProcessQuery () #10 0x000106be9eec in PortalRunMulti () #11 0x000106bea71e in PortalRun () #12 0x000106be8519 in PostgresMain () #13 0x000106ba4ef9 in PostmasterMain () #14 0x000106b418f1 in main () Though I've not read the patch yet, probably lock mechanism in XLogInsert would have a bug which causes the above problem. Regards, -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] 2 bugs with extensions
Marko Kreen mark...@gmail.com writes: Indeed, after another makefile reorg, I could not reproduce it on skytools master either, some digging showed that due to a makefile bug ($ instead $^) the ADD SCHEMA was missing from .sql file. So no bug in postgres. That would explain, indeed :) Could you test if Skytools git now works for you? It does: dim ~/dev/skytools/sql/pgq_coop make pgq_coop--unpackaged--3.1.1.sql ../../scripts/catsql.py structure/upgrade.sql pgq_coop.upgrade.sql ../../scripts/catsql.py pgq_coop.upgrade.sql structure/ext_unpackaged.sql structure/ext_postproc.sql pgq_coop--unpackaged--3.1.1.sql dim ~/dev/skytools/sql/pgq_coop make pgq_coop--3.1.1.sql ../../scripts/catsql.py structure/install.sql pgq_coop.sql ../../scripts/catsql.py pgq_coop.sql structure/ext_postproc.sql pgq_coop--3.1.1.sql I replaced sed usage with awk there, perhaps that will be more portable. I seem to recall needing to explicitly use `gawk` for some scripts, depending on the features you want to have. Some systems default awk are `mawk` or even some really old versions and don't have much features… That said, it seems to work here, now. Thanks! Regards, -- Dimitri Fontaine06 63 07 10 78 http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Patch: incorrect array offset in backend replication tar header
Brian Weaver cmdrcluel...@gmail.com writes: Here's a very minimal fix then, perhaps it will be more palatable. I did some further work on this to improve comments and clean up the pg_dump end of things, and have committed it. Even though I regret the effort I put into the first patch it's in my employer's best interest that it's fixed. I'm obliged to try to remediate the problem in something more acceptable to the community. You're welcome to submit further cleanup as a follow-on patch --- I just want to keep that separate from back-patchable bug fixing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] out of date warnings
I just noticed this code in win32.h and cygwin.h: #if __GNUC__ ! defined (__declspec) #error You need egcs 1.1 or newer for compiling! #endif EGCS was merged back into gcc with the 2.95 release in 1999, according to Wikipedia, So perhaps we should just remove these ancient relics, or at least bring them up to date? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [9.1] 2 bugs with extensions
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Please find attached a patch that fixes it in 9.1, in all classic pg_dump, --data-only and --schema-only. Same for 9.2, attached. master needs yet another patch because of the recent headers reorg, it seems, that's for another day though. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *** *** 189,195 static void dumpTrigger(Archive *fout, TriggerInfo *tginfo); static void dumpTable(Archive *fout, TableInfo *tbinfo); static void dumpTableSchema(Archive *fout, TableInfo *tbinfo); static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo); ! static void dumpSequence(Archive *fout, TableInfo *tbinfo); static void dumpIndex(Archive *fout, IndxInfo *indxinfo); static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo); static void dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo); --- 189,195 static void dumpTable(Archive *fout, TableInfo *tbinfo); static void dumpTableSchema(Archive *fout, TableInfo *tbinfo); static void dumpAttrDef(Archive *fout, AttrDefInfo *adinfo); ! static void dumpSequence(Archive *fout, TableInfo *tbinfo, bool extMember); static void dumpIndex(Archive *fout, IndxInfo *indxinfo); static void dumpConstraint(Archive *fout, ConstraintInfo *coninfo); static void dumpTableConstraintComment(Archive *fout, ConstraintInfo *coninfo); *** *** 1566,1571 dumpTableData(Archive *fout, TableDataInfo *tdinfo) --- 1566,1577 DataDumperPtr dumpFn; char *copyStmt; + if (tbinfo-relkind == RELKIND_SEQUENCE) + { + dumpSequence(fout, tbinfo, true); + return; + } + if (!dump_inserts) { /* Dump/restore using COPY */ *** *** 1638,1646 makeTableDataInfo(TableInfo *tbinfo, bool oids) /* Skip VIEWs (no data to dump) */ if (tbinfo-relkind == RELKIND_VIEW) return; - /* Skip SEQUENCEs (handled elsewhere) */ - if (tbinfo-relkind == RELKIND_SEQUENCE) - return; /* Skip FOREIGN TABLEs (no data to dump) */ if (tbinfo-relkind == RELKIND_FOREIGN_TABLE) return; --- 1644,1649 *** *** 12102,12108 dumpTable(Archive *fout, TableInfo *tbinfo) char *namecopy; if (tbinfo-relkind == RELKIND_SEQUENCE) ! dumpSequence(fout, tbinfo); else if (!dataOnly) dumpTableSchema(fout, tbinfo); --- 12105,12111 char *namecopy; if (tbinfo-relkind == RELKIND_SEQUENCE) ! dumpSequence(fout, tbinfo, false); else if (!dataOnly) dumpTableSchema(fout, tbinfo); *** *** 13219,13225 findLastBuiltinOid_V70(Archive *fout) } static void ! dumpSequence(Archive *fout, TableInfo *tbinfo) { PGresult *res; char *startv, --- 13222,13228 } static void ! dumpSequence(Archive *fout, TableInfo *tbinfo, bool extMember) { PGresult *res; char *startv, *** *** 13319,13325 dumpSequence(Archive *fout, TableInfo *tbinfo) * * Add a 'SETVAL(seq, last_val, iscalled)' as part of a data dump. */ ! if (!dataOnly) { /* * DROP must be fully qualified in case same name appears in --- 13322,13328 * * Add a 'SETVAL(seq, last_val, iscalled)' as part of a data dump. */ ! if (!extMember !dataOnly) { /* * DROP must be fully qualified in case same name appears in *** *** 13440,13446 dumpSequence(Archive *fout, TableInfo *tbinfo) tbinfo-dobj.catId, 0, tbinfo-dobj.dumpId); } ! if (!schemaOnly) { resetPQExpBuffer(query); appendPQExpBuffer(query, SELECT pg_catalog.setval(); --- 13443,13449 tbinfo-dobj.catId, 0, tbinfo-dobj.dumpId); } ! if (extMember || !schemaOnly) { resetPQExpBuffer(query); appendPQExpBuffer(query, SELECT pg_catalog.setval(); *** *** 13953,13963 getExtensionMembership(Archive *fout, ExtensionInfo extinfo[], * the --oids setting. This is because row filtering * conditions aren't compatible with dumping OIDs. */ ! makeTableDataInfo(configtbl, false); ! if (configtbl-dataObj != NULL) ! { ! if (strlen(extconditionarray[j]) 0) ! configtbl-dataObj-filtercond = pg_strdup(extconditionarray[j]); } } } --- 13956,13982 * the --oids setting. This is because row filtering * conditions aren't compatible with dumping OIDs. */ ! switch (configtbl-relkind) ! { ! case RELKIND_SEQUENCE: ! makeTableDataInfo(configtbl, false); ! break; ! ! case RELKIND_RELATION: ! case RELKIND_VIEW: ! makeTableDataInfo(configtbl, false); ! if (configtbl-dataObj != NULL) ! configtbl-dataObj-filtercond = ! pg_strdup(extconditionarray[j]); ! break; ! ! case RELKIND_INDEX: ! case RELKIND_TOASTVALUE: ! case
Re: [HACKERS] [9.1] 2 bugs with extensions
Excerpts from Dimitri Fontaine's message of vie sep 28 17:36:41 -0300 2012: Dimitri Fontaine dimi...@2ndquadrant.fr writes: Please find attached a patch that fixes it in 9.1, in all classic pg_dump, --data-only and --schema-only. Same for 9.2, attached. master needs yet another patch because of the recent headers reorg, it seems, that's for another day though. No, just remove the RELKIND_UNCATALOGUED case in that switch. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER command reworks
Excerpts from Kohei KaiGai's message of lun sep 10 08:08:32 -0300 2012: As attached, I split off the original one into three portions; for set-schema, set-owner and rename-to. Please apply them in order of patch filename. Hmm, in the first patch, it seems to me we can simplify AlterObjectNamespace's signature: instead of passing all the details of the object class (cache Ids and attribute numbers and so on), just do AlterObjectNamespace(catalog-containing-object, objectId, newNamespaceOid) AlterObjectNamespace then looks up the catcache_oid and so on internally. The only difference from what's happening in the submitted patch is that in the AlterCollationNamespace case, AlterObjectNamespace would have to look them up instead of getting them directly from the caller as the patch currently has it. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] out of date warnings
Andrew Dunstan and...@dunslane.net writes: I just noticed this code in win32.h and cygwin.h: #if __GNUC__ ! defined (__declspec) #error You need egcs 1.1 or newer for compiling! #endif EGCS was merged back into gcc with the 2.95 release in 1999, according to Wikipedia, So perhaps we should just remove these ancient relics, or at least bring them up to date? +1 for removing it. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] embedded list v2
On Friday, September 14, 2012 10:57:54 PM Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: One thing I would like more input in, is whether people think it's worthwhile to split dlists and slists into separate files. Thus far this has been mentioned by three people independently. They're small enough and similar enough that one header and one .c file seem like plenty; but I don't really have a strong opinion about it. Another question is whether ilist_container() should actually be a more general macro containerof defined in c.h. (ISTM it would be necessary to have this macro if we want to split into two files; that way we don't need to have two macros dlist_container and slist_container with identical definition, or alternatively a third file that defines just ilist_container) I'd vote for not having that at all, but rather two separate macros dlist_container and slist_container. If we had a bunch of operations that could work interchangeably on dlists and slists, it might be worth having a concept of ilist --- but if we only have this, it would be better to remove the concept from the API altogether. Third question is about the INLINE_IF_POSSIBLE business as commented by Peter. It seems to me that the simple technique used here to avoid having two copies of the source could be used by memcxt.c, list.c, sortsupport.c as well (maybe clean up fastgetattr too). Yeah, looks reasonable ... material for a different patch of course. But that would mean INLINE_IF_POSSIBLE should be defined someplace else, perhaps c.h. Also, I'm not that thrilled with having the header file define ILIST_USE_DEFINITION. I suggest that it might be better to do #if defined(USE_INLINE) || defined(DEFINE_ILIST_FUNCTIONS) ... function decls here ... #else ... extern decls here ... #endif where ilist.c defines DEFINE_ILIST_FUNCTIONS before including the header. I am preparing a new version of this right now. So, some last ditch questions are coming up... The reason I had the header declare DEFINE_ILIST_FUNCTIONS (or rather ILIST_USE_DEFINITION back then) instead of reusing USE_INLINE directly is that it makes it easier to locally change a module to not inlining which makes testing the !USE_INLINE case easier. Does anybody think this is worth something? I have no strong feelings but found it convenient. Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Generalizing range-constraint detection in clauselist_selectivity
Over in pgsql-performance, Shaun Thomas was just complaining about the planner not picking a bitmap indexscan for a query involving a constraint like b.created_dt between a.created_dt and a.created_dt + interval '1 month'; At first I wrote this off as being due to inability to get a good selectivity estimate, but on second look it seemed like even with the default estimate for a range constraint, the planner should've made the choice he wanted. After a bit of digging I realized that it wasn't recognizing this as a range constraint on b.created_dt at all, because the code in clauselist_selectivity that tries to pair up inequality constraints punts altogether for anything involving a join --- it only wants to look at var = constant types of clauses: * See if it looks like a restriction clause with a pseudoconstant on * one side. (Anything more complicated than that might not behave in * the simple way we are expecting.) I'm thinking that this is overly restrictive, and we could usefully suppose that var = anything and var = anything should be treated as a range constraint pair if the vars match and there are no volatile functions in the expressions. We are only trying to get a selectivity estimate here, so rigorous correctness is not required. However, I'm a little worried that I might be overlooking cases where this would be unduly optimistic. Does anyone see a situation where such a pair of clauses *shouldn't* be thought to be a range constraint on the var? For instance, should we still restrict the var side to be an expression in columns of only one relation? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] embedded list v2
Andres Freund and...@2ndquadrant.com writes: The reason I had the header declare DEFINE_ILIST_FUNCTIONS (or rather ILIST_USE_DEFINITION back then) instead of reusing USE_INLINE directly is that it makes it easier to locally change a module to not inlining which makes testing the !USE_INLINE case easier. Does anybody think this is worth something? I have no strong feelings but found it convenient. Right offhand it doesn't seem like it really gains that much even for that use-case. You'd end up editing the include file either way, just slightly differently. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] embedded list v2
On Saturday, September 29, 2012 01:39:03 AM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: The reason I had the header declare DEFINE_ILIST_FUNCTIONS (or rather ILIST_USE_DEFINITION back then) instead of reusing USE_INLINE directly is that it makes it easier to locally change a module to not inlining which makes testing the !USE_INLINE case easier. Does anybody think this is worth something? I have no strong feelings but found it convenient. Right offhand it doesn't seem like it really gains that much even for that use-case. You'd end up editing the include file either way, just slightly differently. Well, with USE_INLINE you have to recompile the whole backend because you otherwise easily end up with strange incompatibilities between files. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Generalizing range-constraint detection in clauselist_selectivity
I'm thinking that this is overly restrictive, and we could usefully suppose that var = anything and var = anything should be treated as a range constraint pair if the vars match and there are no volatile functions in the expressions. We are only trying to get a selectivity estimate here, so rigorous correctness is not required. However, I'm a little worried that I might be overlooking cases where this would be unduly optimistic. Does anyone see a situation where such a pair of clauses *shouldn't* be thought to be a range constraint on the var? For instance, should we still restrict the var side to be an expression in columns of only one relation? Hmmm. I don't see why we have to restrict them, at least in theory. If more than one relation is involved in an expression for var, then doesn't the join between the other relations have to be evaluated prior to evaluating the join conditions on the range relation? i.e. it seems to me that for relations a,b,c: where ( a.1 + b.1 ) = c.1 and ( a.2 + b.2 ) = c.1 ... that we're already forced to join a and b before we can meaningfully evaluate the join condition on c, no? If not, then we do have to restrict, but it seems to me that we are. Other than that, I can't come up with a real problem for this optimization which wouldn't already be disqualified (like types which evaluate = in a non-scalar manner). -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] embedded list v2
Andres Freund and...@2ndquadrant.com writes: On Saturday, September 29, 2012 01:39:03 AM Tom Lane wrote: Right offhand it doesn't seem like it really gains that much even for that use-case. You'd end up editing the include file either way, just slightly differently. Well, with USE_INLINE you have to recompile the whole backend because you otherwise easily end up with strange incompatibilities between files. Eh? You would anyway, or at least recompile every .o file depending on that header, if what you want is to inline or de-inline the functions. There's no magic shortcut for that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Generalizing range-constraint detection in clauselist_selectivity
Josh Berkus j...@agliodbs.com writes: I'm thinking that this is overly restrictive, and we could usefully suppose that var = anything and var = anything should be treated as a range constraint pair if the vars match and there are no volatile functions in the expressions. We are only trying to get a selectivity estimate here, so rigorous correctness is not required. However, I'm a little worried that I might be overlooking cases where this would be unduly optimistic. Does anyone see a situation where such a pair of clauses *shouldn't* be thought to be a range constraint on the var? For instance, should we still restrict the var side to be an expression in columns of only one relation? Hmmm. I don't see why we have to restrict them, at least in theory. If more than one relation is involved in an expression for var, then doesn't the join between the other relations have to be evaluated prior to evaluating the join conditions on the range relation? i.e. it seems to me that for relations a,b,c: where ( a.1 + b.1 ) = c.1 and ( a.2 + b.2 ) = c.1 ... that we're already forced to join a and b before we can meaningfully evaluate the join condition on c, no? If not, then we do have to restrict, but it seems to me that we are. Well, one point that I'm not too sure about the implications of is that in practice, clauselist_selectivity is not called on random collections of clauses, but only clauses that are all going to be evaluated at the same place, ie a particular scan or join. So that's already going to limit the combinations of clauses that it can be pointed at. An example of why this might be an issue is a.x = b.y AND a.x = constant If we change things as I'm thinking, these two clauses would be seen as a range pair, but only when they appear in the same clause list. And most of the time they wouldn't --- a.x = constant would drop down to the restriction clause list for a, but the first clause would be kept in the a+b join clause list. This means the size of the a+b join relation would be estimated without recognizing the range relationship. But then, if we considered a parameterized indexscan on a.x, it would have both clauses in its indexqual list, so we'd use the range interpretation in costing that indexscan, which would likely give that particular plan an unfair advantage. Maybe that's just fine, or maybe it isn't. I'm not sure. We could probably eliminate that inconsistency by insisting that two clauses can only be matched for this purpose when they reference the same set of rels overall, but that doesn't feel right --- it certainly seems like the example above ought to be thought of as a range restriction if possible. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] embedded list v2
On Saturday, September 29, 2012 01:54:37 AM Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On Saturday, September 29, 2012 01:39:03 AM Tom Lane wrote: Right offhand it doesn't seem like it really gains that much even for that use-case. You'd end up editing the include file either way, just slightly differently. Well, with USE_INLINE you have to recompile the whole backend because you otherwise easily end up with strange incompatibilities between files. Eh? You would anyway, or at least recompile every .o file depending on that header, if what you want is to inline or de-inline the functions. There's no magic shortcut for that. Well, --enable-depend copes with changing that in the header fine. As long as its only used in a low number of files thats shorter than a full rebuild ;) Anyway, changed. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] embedded list v3
Add [ds]list's which can be used to embed lists in bigger data structures without additional memory management Alvaro, Andres, Review by Peter G. and Tom This is missing Robert. Sorry for that. Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Generalizing range-constraint detection in clauselist_selectivity
On 9/28/12 5:13 PM, Tom Lane wrote: We could probably eliminate that inconsistency by insisting that two clauses can only be matched for this purpose when they reference the same set of rels overall, but that doesn't feel right --- it certainly seems like the example above ought to be thought of as a range restriction if possible. Yes, it does. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] data to json enhancements
Hi Guys, I have made some blog about the subject: http://misasimic.blogspot.co.uk/2012/09/postgresql-92-and-json-datatype.html Hopefully will help on some kind... Kind Regards, Misa
Re: [HACKERS] data to json enhancements
On 09/28/2012 10:34 PM, Misa Simic wrote: Hi Guys, I have made some blog about the subject: http://misasimic.blogspot.co.uk/2012/09/postgresql-92-and-json-datatype.html Hopefully will help on some kind... I think if you want to contribute you should post on the mailing list - otherwise the conversation just becomes way too fragmented. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers