[GENERAL] indexed range queries on jsonb?
Hi, I'm trying to find a way to do a range query on json such that it will use an index. This seems possible given that jsonb supports btrees and expression indices. For example I have: create index t1 on document using btree ((payload-'intTest')); where: payload is a jsonb column and intTest is a json key whose value is always an int. Based on the documentation examples, I created an index like this: create index t1 on document using btree ((payload-'intTest')); Logically, what I want is to be able to make queries like this: select * from document where ((payload-'intTest')) 5; With casting, I came up with: select * from document where (((payload-'intTest'))::text)::integer 5; But this query does not use the index according to Explain Seq Scan on public.document (cost=0.00..1868.33 rows=5764 width=619) (actual time=286.228..1706.638 rows=1974 loops=1) Output: owner, document_type, guid, schema_version, payload, last_update, payload_class, instance_version, acl_read, deleted, fts_text Filter: document.payload - 'intTest'::text))::text)::integer 5) Rows Removed by Filter: 15319 Buffers: shared hit=5420 read=29085 Planning time: 0.108 ms Execution time: 1706.941 ms Any help at all would be appreciated. Thanks.
Re: [GENERAL] indexed range queries on jsonb?
Thank you Tom, I made the necessary changes and Explain now shows that the query will use the index. Thanks again for your help. On Tue, Aug 26, 2014 at 10:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: Larry White ljw1...@gmail.com writes: Logically, what I want is to be able to make queries like this: select * from document where ((payload-'intTest')) 5; With casting, I came up with: select * from document where (((payload-'intTest'))::text)::integer 5; But this query does not use the index according to Explain Nope. You would have to create an index on the casted expression if you want to use integer comparisons with the index. The raw - expression is of type jsonb, which doesn't sort the same as integer. BTW, you could save a small amount of notation with the - operator, ie (payload-'intTest')::integer regards, tom lane
[GENERAL] JsonB Gin Index is very large; is there a work around?
Hi, I'm using 9.4 beta 2. I ran a test using 4 of the largest Json docs from our production data set. The four files total to 59.4 MB of raw json, which is compressed by TOAST to 21 MB, which is great. The index, though, is 47 MB, bringing the total size of the data in PG to 68 MB. The index was created as: CREATE INDEX document_payload_idx ON document USING gin (payload jsonb_path_ops); I recognize that these may be reasonably considered pathological cases. My questions are: Is the work that was done to reduce GIN index size in this release? Is there anyway to index a subset of the data in a JSONB column? I'm thinking of something like declaring certain paths to be indexed? Any suggestions would be greatly appreciated.
Re: [GENERAL] JsonB Gin Index is very large; is there a work around?
Ok Thank you.
FWIW, the documents (which I can't share) consist mainly of a long list of
integers in the form {n:41515920318427252715}, so they really are
outliers.
On Wed, Aug 20, 2014 at 5:09 PM, Peter Geoghegan
peter.geoghega...@gmail.com wrote:
On Wed, Aug 20, 2014 at 1:53 PM, Larry White ljw1...@gmail.com wrote:
Is there anyway to index a subset of the data in a JSONB column? I'm
thinking of something like declaring certain paths to be indexed?
Yes. See the expression index example in the jsonb documentation.
--
Regards,
Peter Geoghegan
Re: [GENERAL] New wrapper library: QUINCE
I'm not a C++ developer, but this looks like a really well-designed API. Nice work. On Mon, Aug 18, 2014 at 9:21 PM, Jov am...@amutu.com wrote: A cool project! Jov blog: http:amutu.com/blog http://amutu.com/blog 2014-08-19 6:43 GMT+08:00 Michael Shepanski m...@optusnet.com.au: Hi PostgreSQLers, I've released an open-source library called quince (QUeries In C++ Expressions) that helps you access PostgreSQL from C++. I know, I know: you've got plenty of those already, but this one is different ... It's an EDSL (Embedded Domain-Specific Language), which lets you build sophisticated SQL queries in C++ syntax with C++ data types, and it's also an ORM, which figures out how to represent your C++ structs/classes/tuples as multiple columns. It's a plain old library, so no special compiler and no code generation step. And fwiw it works with sqlite too (and maybe other DBMSes in the future -- it's a matter of adding backend libraries). It's all explained at http://quince-lib.com . Cheers, --- Michael Shepanski -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Anyone using Apache Helix to manage a PostgreSQL cluster?
Very interested in hearing of successful or unsuccessful attempts, issues, etc. Thanks very much. larry
Re: [GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)
On Fri, Aug 1, 2014 at 2:20 AM, Jeff Janes jeff.ja...@gmail.com wrote:
On Thursday, July 31, 2014, Larry White ljw1...@gmail.com wrote:
Hi,
I'm running an experiment on 9.4 beta 2.
I put 275,000 identical JSON files into a table using JSONB (one per
row). Each raw text file is 251K in size, so the total uncompressed is
69GB. The column storage is set to EXTENDED. There are other toastable
columns in the table, but none have more than 36 bytes of data in them.
My Toast table is 66GB. I would have expected to get that much (or more)
compression just from JSONB being a binary format.
If I compress one of these JSON files outside of Postgres, it goes from
251K to 1K.
That is an astonishing amount of compression. Are you really compressing
one of the files in isolation, rather than co-compressing many and then
pro-rating the result?
Yes, I should have explained. These are generated JSON files for testing
and there is massive repetition in them, which is why they compress so well
outside of Postgres. (Basically there is a repeating array of the same
string) I did compress just the one.
Can you provide an example of the data, and the command line you used to
compress it?
Compressed on a Mac with the Compress UI option. Here's a brief sample from
the file. You can see why it compresses so well:
{\junk\:[\124245etweetwet345gwtretwt43 qwrqwq qwre qw
rsdflkas\,\q4535 wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
and so on.
So each file should theoretically fit on a single row in the toast table.
In total, the amount well under a GB when compressed outside of PG.
Any guesses as to why there is so little compression of this data or how
I might remedy the situation?
PostgreSQL's built in tuple compression is generally not very good. It is
good at compressing long strings of identical bytes, but not good at
compressing the type of thing you are likely to find in JSON (unless your
JSON had long strings of spaces to reflect indentation of deeply nested
structures, which JSON probably wouldn't do and which JSONB certainly
wouldn't). It was designed to be very fast and to be unencumbered with the
patent issues common at the time it was written. It was not designed to
give the best possible compression ratios.
It also compresses each row independently. Most of the compression
opportunities in a column of JSON data would probably be between rows, when
the same keys show up and over and over again, not within a row. But it
can't capture those opportunities.
I'm not expecting miracles with real data, but as far as I can tell, there
is zero compression happening. I'm wondering if it is disabled for JSONB
for some reason.
Cheers,
Jeff
Re: [GENERAL] Re: Is it possible to create an index without keeping the indexed data in a column?
Thank you David and Amit. This is more or less what I was looking for. I _think_ I might be able to store the data as TEXT, which is highly compressed by Toast, and then perhaps write the function in terms of a TEXT to JSONB conversion. I will give it a try. It might perform terribly, but will be an interesting experiment.:) On Fri, Aug 1, 2014 at 3:14 AM, David G Johnston david.g.johns...@gmail.com wrote: larrry wrote Hi, I would like to create a GIN index on a set of JSON documents. Right now I'm storing the data in a JSONB column. The current index looks like this: CREATE INDEX document_payload_idx ON document USING gin (payload jsonb_path_ops); The index is pretty small, but the actual data takes up a *lot* of space. Is there a way to get Postgres to index the table *as if* the JSON were there, but not actually put the data in the table? I could either store the docs elsewhere and keep a reference, or compress them and put them in the table in compressed form as a blob. Thanks much for your help. Larry No idea if this works but maybe you can store the compressed data and then write the index expression like: USING gin (unzip(payload) jsonb_path_ops) The unzip function would need to be custom I think... David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Is-it-possible-to-create-an-index-without-keeping-the-indexed-data-in-a-column-tp5813461p5813500.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] Very Limited Toast Compression on JSONB (9.4 beta 2)
There is no TOAST compression on JSON or JSONB data in 9.4 beta 2. I'm not
sure about other versions. I'm also not sure if this is a bug or by
design, but if it is by design, I think the documentation should be
updated.
Here is a summary of my results inserting 10,000 highly compressible JSON
docs of 251K each.
Column Type - Storage - TOAST table size
JSONB - EXTERNAL - 2448 MB
JSONB - EXTENDED - 2448 MB
JSON - EXTENDED - 2504 MB
TEXT - EXTERNAL - 2409 MB
TEXT - EXTENDED - 40 MB
On Fri, Aug 1, 2014 at 2:36 AM, Larry White ljw1...@gmail.com wrote:
On Fri, Aug 1, 2014 at 2:20 AM, Jeff Janes jeff.ja...@gmail.com wrote:
On Thursday, July 31, 2014, Larry White ljw1...@gmail.com wrote:
Hi,
I'm running an experiment on 9.4 beta 2.
I put 275,000 identical JSON files into a table using JSONB (one per
row). Each raw text file is 251K in size, so the total uncompressed is
69GB. The column storage is set to EXTENDED. There are other toastable
columns in the table, but none have more than 36 bytes of data in them.
My Toast table is 66GB. I would have expected to get that much (or more)
compression just from JSONB being a binary format.
If I compress one of these JSON files outside of Postgres, it goes from
251K to 1K.
That is an astonishing amount of compression. Are you really compressing
one of the files in isolation, rather than co-compressing many and then
pro-rating the result?
Yes, I should have explained. These are generated JSON files for testing
and there is massive repetition in them, which is why they compress so well
outside of Postgres. (Basically there is a repeating array of the same
string) I did compress just the one.
Can you provide an example of the data, and the command line you used to
compress it?
Compressed on a Mac with the Compress UI option. Here's a brief sample
from the file. You can see why it compresses so well:
{\junk\:[\124245etweetwet345gwtretwt43 qwrqwq qwre qw
rsdflkas\,\q4535 wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
and so on.
So each file should theoretically fit on a single row in the toast
table. In total, the amount well under a GB when compressed outside of PG.
Any guesses as to why there is so little compression of this data or how
I might remedy the situation?
PostgreSQL's built in tuple compression is generally not very good. It
is good at compressing long strings of identical bytes, but not good at
compressing the type of thing you are likely to find in JSON (unless your
JSON had long strings of spaces to reflect indentation of deeply nested
structures, which JSON probably wouldn't do and which JSONB certainly
wouldn't). It was designed to be very fast and to be unencumbered with the
patent issues common at the time it was written. It was not designed to
give the best possible compression ratios.
It also compresses each row independently. Most of the compression
opportunities in a column of JSON data would probably be between rows, when
the same keys show up and over and over again, not within a row. But it
can't capture those opportunities.
I'm not expecting miracles with real data, but as far as I can tell, there
is zero compression happening. I'm wondering if it is disabled for JSONB
for some reason.
Cheers,
Jeff
[GENERAL] jsonb creation functions?
There is a set of creation functions for json, such as: to_json(anyelement) There doesn't seem to be any equivalent functions for converting text to jsonb. Is there a way to do this? Thanks.
Re: [GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)
Jeff,
Thank you for your help. This is a Postgres bug, but I don't think I'd have
figured it out without your help.
What is happening is that if PG can, after compression, put the entire
'document' into one row/page in the toast table it does. However, if the
document is too big to fit in one row after compression, it does no
compression at all. This is why it worked for you, but not for me.
I create my test file (in part) with this loop:
for (int j = 0; j 110; j++) {
mediumPayload.getJunk().add(124245etweetwet345gwtretwt43
qwrqwq qwre qw rsdflkas);
mediumPayload.getJunk().add(q4535 wqrqwrqwrqw2 wrqwrqwrq32232w
kswe sfasrs sdfsd);
}
if the loop runs 110 times as shown, it compresses.
if the loop runs 111 times, it does not:
With 110 iterations:
Extended 8192 bytes (one page)
External 66 MB
With 111 iterations:
Extended 69 MB
External69 MB
Hopefully they can fix this before the GA release.
On Fri, Aug 1, 2014 at 12:38 PM, Jeff Janes jeff.ja...@gmail.com wrote:
On Thu, Jul 31, 2014 at 11:36 PM, Larry White ljw1...@gmail.com wrote:
On Fri, Aug 1, 2014 at 2:20 AM, Jeff Janes jeff.ja...@gmail.com wrote:
On Thursday, July 31, 2014, Larry White ljw1...@gmail.com wrote:
Hi,
I'm running an experiment on 9.4 beta 2.
I put 275,000 identical JSON files into a table using JSONB (one per
row). Each raw text file is 251K in size, so the total uncompressed is
69GB. The column storage is set to EXTENDED. There are other toastable
columns in the table, but none have more than 36 bytes of data in them.
My Toast table is 66GB. I would have expected to get that much (or
more) compression just from JSONB being a binary format.
If I compress one of these JSON files outside of Postgres, it goes from
251K to 1K.
That is an astonishing amount of compression. Are you really
compressing one of the files in isolation, rather than co-compressing many
and then pro-rating the result?
Yes, I should have explained. These are generated JSON files for testing
and there is massive repetition in them, which is why they compress so well
outside of Postgres. (Basically there is a repeating array of the same
string) I did compress just the one.
Can you provide an example of the data, and the command line you used to
compress it?
Compressed on a Mac with the Compress UI option. Here's a brief sample
from the file. You can see why it compresses so well:
{\junk\:[\124245etweetwet345gwtretwt43 qwrqwq qwre qw
rsdflkas\,\q4535 wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
and so on.
If I take that example (and cap off the array and hash right after the end
of what you show, and remove the escapes of the double quote marks) then it
does not compress, but only because it is not long enough to trigger the
compression attempts.
If I repeat the array portion 4 more times to make the whole thing long
enough for compression to be used, it compresses nicely. Not 100 fold (but
then again, neither does bzip2 or gzip on the data I just described), but
text and json compresses 10 fold and jsonb 5 fold.
Cheers,
Jeff
Re: [GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)
Reported as bug #11109.
On Fri, Aug 1, 2014 at 1:46 PM, Larry White ljw1...@gmail.com wrote:
Jeff,
Thank you for your help. This is a Postgres bug, but I don't think I'd
have figured it out without your help.
What is happening is that if PG can, after compression, put the entire
'document' into one row/page in the toast table it does. However, if the
document is too big to fit in one row after compression, it does no
compression at all. This is why it worked for you, but not for me.
I create my test file (in part) with this loop:
for (int j = 0; j 110; j++) {
mediumPayload.getJunk().add(124245etweetwet345gwtretwt43
qwrqwq qwre qw rsdflkas);
mediumPayload.getJunk().add(q4535 wqrqwrqwrqw2
wrqwrqwrq32232w kswe sfasrs sdfsd);
}
if the loop runs 110 times as shown, it compresses.
if the loop runs 111 times, it does not:
With 110 iterations:
Extended 8192 bytes (one page)
External 66 MB
With 111 iterations:
Extended 69 MB
External69 MB
Hopefully they can fix this before the GA release.
On Fri, Aug 1, 2014 at 12:38 PM, Jeff Janes jeff.ja...@gmail.com wrote:
On Thu, Jul 31, 2014 at 11:36 PM, Larry White ljw1...@gmail.com wrote:
On Fri, Aug 1, 2014 at 2:20 AM, Jeff Janes jeff.ja...@gmail.com wrote:
On Thursday, July 31, 2014, Larry White ljw1...@gmail.com wrote:
Hi,
I'm running an experiment on 9.4 beta 2.
I put 275,000 identical JSON files into a table using JSONB (one per
row). Each raw text file is 251K in size, so the total uncompressed is
69GB. The column storage is set to EXTENDED. There are other toastable
columns in the table, but none have more than 36 bytes of data in them.
My Toast table is 66GB. I would have expected to get that much (or
more) compression just from JSONB being a binary format.
If I compress one of these JSON files outside of Postgres, it goes
from 251K to 1K.
That is an astonishing amount of compression. Are you really
compressing one of the files in isolation, rather than co-compressing many
and then pro-rating the result?
Yes, I should have explained. These are generated JSON files for testing
and there is massive repetition in them, which is why they compress so well
outside of Postgres. (Basically there is a repeating array of the same
string) I did compress just the one.
Can you provide an example of the data, and the command line you used
to compress it?
Compressed on a Mac with the Compress UI option. Here's a brief sample
from the file. You can see why it compresses so well:
{\junk\:[\124245etweetwet345gwtretwt43 qwrqwq qwre qw
rsdflkas\,\q4535 wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
wqrqwrqwrqw2 wrqwrqwrq32232w kswe sfasrs
sdfsd\,\124245etweetwet345gwtretwt43 qwrqwq qwre qw rsdflkas\,\q4535
and so on.
If I take that example (and cap off the array and hash right after the
end of what you show, and remove the escapes of the double quote marks)
then it does not compress, but only because it is not long enough to
trigger the compression attempts.
If I repeat the array portion 4 more times to make the whole thing long
enough for compression to be used, it compresses nicely. Not 100 fold (but
then again, neither does bzip2 or gzip on the data I just described), but
text and json compresses 10 fold and jsonb 5 fold.
Cheers,
Jeff
[GENERAL] Is it possible to create an index without keeping the indexed data in a column?
Hi, I would like to create a GIN index on a set of JSON documents. Right now I'm storing the data in a JSONB column. The current index looks like this: CREATE INDEX document_payload_idx ON document USING gin (payload jsonb_path_ops); The index is pretty small, but the actual data takes up a *lot* of space. Is there a way to get Postgres to index the table *as if* the JSON were there, but not actually put the data in the table? I could either store the docs elsewhere and keep a reference, or compress them and put them in the table in compressed form as a blob. Thanks much for your help. Larry
[GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)
Hi, I'm running an experiment on 9.4 beta 2. I put 275,000 identical JSON files into a table using JSONB (one per row). Each raw text file is 251K in size, so the total uncompressed is 69GB. The column storage is set to EXTENDED. There are other toastable columns in the table, but none have more than 36 bytes of data in them. My Toast table is 66GB. I would have expected to get that much (or more) compression just from JSONB being a binary format. If I compress one of these JSON files outside of Postgres, it goes from 251K to 1K. So each file should theoretically fit on a single row in the toast table. In total, the amount well under a GB when compressed outside of PG. Any guesses as to why there is so little compression of this data or how I might remedy the situation? Thanks much for your help. Larry
Re: [GENERAL] Very Limited Toast Compression on JSONB (9.4 beta 2)
Yes. It was EXTENDED. As a further test, I dropped the table and rebuilt it, explicitly changing the EXTENDED designation to EXTERNAL and got exactly the same size TOAST table. So there was no compression at all with storage set to EXTENDED. On Thu, Jul 31, 2014 at 11:51 PM, Adrian Klaver adrian.kla...@aklaver.com wrote: On 07/31/2014 01:44 PM, Larry White wrote: Hi, I'm running an experiment on 9.4 beta 2. I put 275,000 identical JSON files into a table using JSONB (one per row). Each raw text file is 251K in size, so the total uncompressed is 69GB. The column storage is set to EXTENDED. There are other toastable columns in the table, but none have more than 36 bytes of data in them. My Toast table is 66GB. I would have expected to get that much (or more) compression just from JSONB being a binary format. If I compress one of these JSON files outside of Postgres, it goes from 251K to 1K. So each file should theoretically fit on a single row in the toast table. In total, the amount well under a GB when compressed outside of PG. Any guesses as to why there is so little compression of this data or how I might remedy the situation? Are you sure the column storage is EXTENDED and not EXTERNAL? Thanks much for your help. Larry -- Adrian Klaver adrian.kla...@aklaver.com
[GENERAL] inequality testing in jsonb query
Hi,
Is it possible to query a table with a jsob column to find values that were
in some range? For example, If I have a document like this (from the PG
documentation:
{
guid: 9c36adc1-7fb5-4d5b-83b4-90356a46061a,
name: Angela Barton,
is_active: true,
company: Magnafone,
address: 178 Howard Place, Gulf, Washington, 702,
registered: 2009-11-07T08:53:22 +08:00,
latitude: 19.793713,
longitude: 86.513373,
tags: [
enim,
aliquip,
qui
]
}
Could I modify the following query to find those records where the date
registered is between November 1, 2009 and November 30, 2009?
SELECT jdoc-'guid', jdoc-'name' FROM api WHERE jdoc @ '{company:
Magnafone}';
Thank you for your help.
larry
[GENERAL] passing parameters to a trigger function
I can't figure out how to pass parameters to a trigger function. I checked the documentation and saw that trigger functions don't take params in the usual fashion, but couldn't find an example of a pl-sql trigger function that used the original row data within the function. What I want is an on update trigger that creates an entry in a second table. The second (history) table has a subset of the columns in the first.Here's what I have so far: -- THIS IS WRONG - CANT PASS PARAMS INTO TRIGGER FUNCTION CREATE OR REPLACE FUNCTION audit_task (param type declarations were here) RETURNS TRIGGER AS ' -- create an audit trail recordBEGIN -- Perform the insert INSERT INTO TASK_h (id, updated_by, updated, name, description ) VALUES ($1, $2, $3, $4, $5); RETURN NULL; END;' LANGUAGE plpgsql;-- THE TRIGGERCREATE TRIGGER t_audit_task AFTER INSERT OR UPDATE ON task FOR EACH ROW EXECUTE PROCEDURE audit_task(); So the question is, how do I access the row from the original table so I can perform the insert? Thank you much.
Re: [GENERAL] passing parameters to a trigger function (solved)
Thank you. You guys rock!On 3/21/06, Stephan Szabo [EMAIL PROTECTED] wrote: On Tue, 21 Mar 2006, Larry White wrote: I can't figure out how to pass parameters to a trigger function. I checked the documentation and saw that trigger functions don't take params in the usual fashion, but couldn't find an example of a pl-sql trigger function that used the original row data within the function. What I want is an on update trigger that creates an entry in a second table.The second (history)table has a subset of the columns in the first. Here's what I have so far: -- THIS IS WRONG - CANT PASS PARAMS INTO TRIGGER FUNCTION CREATE OR REPLACE FUNCTION audit_task (param type declarations were here) RETURNS TRIGGER AS ' -- create an audit trail record BEGIN -- Perform the insert INSERT INTO TASK_h(id,updated_by,updated,name, description) VALUES ($1, $2, $3, $4, $5); RETURN NULL; END; ' LANGUAGE plpgsql; -- THE TRIGGER CREATE TRIGGER t_audit_task AFTER INSERT OR UPDATE ON task FOR EACH ROW EXECUTE PROCEDURE audit_task(); So the question is, how do I access the row from the original table so I can perform the insert?The old row is OLD and the new row NEW and do not need to be declared asarguments (in fact trigger functions are always currently created withoutdeclared arguments).I think section 36.10 in the 8.1 docs has info forother implicit arguments to plpgsql trigger functions.
[GENERAL] view creation question
I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like Select name, description, date from Tasks; Select name, description, date from Issues; Is there some way to wrap these two independent queries in a CREATE VIEW myview AS statement?
[GENERAL] Is the FTP server down?
I get time out errors from all mirrors when trying to download 8.0 win binaries. Apologies if this is the wrong forum for this. thanks. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] transactions, functions, foreign keys
Hi, I've run into a situation (I should have forseen) and was hoping someone could show me a way out. I have a function that calls other functions. These other functions are inserting rows and return the primary key for the inserted row. Some of the tables are related in a way that they have a foreign key reference to a table that was updated in a previous step. Here's an example in psuedocode create function foo() AS ' begin select into key1 bar1( a, b); select into key2 bar2,(e, f, key1); etc... end ' The call to bar2 uses the key from the call to bar1. The table updated in bar2 has a foreign key constraint referencing the key1 column from bar1, but the bar1 transaction hasn't been committed. Thus - a foreign key violation exception. (That's the part I should have seen coming.) Is there anyway to cleanly handle this kind of situation?I'm working on the initialization piece of a fairly complex database and there are a large number of these relations to setup. I'd prefer not to have to call each separately from the command line because of the possibility of error. They could also be called sequentially in a .sql file, but there's no way to pass variables between them then. Thanks for your help. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] transactions, functions, foreign keys
My mistake. There was something else going on and I misinterpreted the cause of the problem. The foreign key references are valid within the transaction even though the initial updates are not yet committed. apologies for the distraction and thanks again for your help. On Wed, 15 Dec 2004 10:33:57 -0700, Michael Fuhr [EMAIL PROTECTED] wrote: On Wed, Dec 15, 2004 at 11:53:55AM -0500, Larry White wrote: I have a function that calls other functions. These other functions are inserting rows and return the primary key for the inserted row. Some of the tables are related in a way that they have a foreign key reference to a table that was updated in a previous step. Here's an example in psuedocode create function foo() AS ' begin select into key1 bar1( a, b); select into key2 bar2,(e, f, key1); etc... end ' The call to bar2 uses the key from the call to bar1. The table updated in bar2 has a foreign key constraint referencing the key1 column from bar1, but the bar1 transaction hasn't been committed. Thus - a foreign key violation exception. (That's the part I should have seen coming.) Have you actually seen this problem? I don't know if PostgreSQL's visibility rules have changed, but I just tested what you describe in 7.4.6 and 8.0.0rc1 and it works. What version of PostgreSQL are you using? Could you post a complete example that exhibits the problem? -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] using inheritance in production application.
How 'ready for prime-time' is the table inheritance feature? I've seen some postings about particular issues (lack of full FK support, for example), but would like to get an overall sense of the stability and robustness of the feature. Also, is there a performance hit in using inheritance? For example if I have a sub-table that adds a couple of columns to a base table, would it be slower to query that structure than it would to represent the extra columns as a separate table that would be joined with the base table? thanks for your help. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Newbie question: returning rowtypes from a plpgsql function
Please excuse my ignorance of databases and black holes. I don't have access to a Postgres db right now so I tried an experiment with mysql. Since they don't have a select into that creates a table, I tried this: mysql create table t as ( select * from table_x); since table_x has no rows, I get: Query OK, 0 rows affected (0.06 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql show tables; | Tables_in_test| | table_x| | t| +--+ 2 rows in set (0.02 sec) So it creates a table called t with no records and the same structure as table_x. That's what I thought the postgresql SELECT INTO would do. Now that I looked at the documentation more closely, I see that SELECT INTO returns a table when used in a query but an array of values when used in plpgsql, so that's at least part of what I have wrong. Having given it more thought, I think another error was to not declare the function as returning SETOF, so I can give that a try later. The declarations section of the pl/pgsql documentation doesn't explain how to declare a variable to represent a set of rows so if anyone can suggest something that would be helpful. Thanks. On Tue, 30 Nov 2004 22:58:11 -0500, Tom Lane [EMAIL PROTECTED] wrote: Larry White [EMAIL PROTECTED] writes: I wrote a function that returns a rowtype. The rowtype is assigned a value by a query using SELECT INTO. The query sometimes will return no rows. When it does, the function's return value is a row with no values. I would have expected it to return 0 rows, like the query itself. How exactly would SELECT INTO return 0 rows? Perhaps the target variables vanish into a black hole? regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Newbie question: returning rowtypes from a plpgsql function
I wrote a function that returns a rowtype. The rowtype is assigned a value by a query using SELECT INTO. The query sometimes will return no rows. When it does, the function's return value is a row with no values. I would have expected it to return 0 rows, like the query itself. Am I doing something wrong or is this the expected behavior? Is there a standard way to code around this? I expected my client code to check the number of rows returned to decide what to do next. thanks ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Nesting Stored Procedure Calls
Is it possible for one stored procedure to call another? I would like certain procs to call a different proc to update an audit trail whenever they're executed. I thought about using triggers but want the trail to include info that's not in the updated table - specifically the application user ID of the responsible party. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Nesting Stored Procedure Calls
Is it possible for one stored procedure to call another? I would like a number of procs to call another proc to update an audit trail whenever they're called. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
