Re: [GENERAL] [Q] parsing out String array
V S P wrote:
if I have field declared
myvalue text[][]
insert into vladik (myval)
values
(
'{{"\",A", "\"B"}, {"Y", "Q"}}'
)
What do you guys use in your treasurechest of 'addons'
to successfully parse out the above trickery
and get
and get the 4 strings
",A
"B
Y
Q
from within Postgres stored procedure as well as C++ or other client
code.
It seems to me that it is not possible with any built-in command
available
to easily extract the strings out to a multidimensional array
Actually its pretty easy.
SELECT myval[i][j]
FROM vladik
CROSS JOIN generate_series(1, array_upper(myval, 1)) i
CROSS JOIN generate_series(1, array_upper(myval, 2)) j
--
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] Looping through string constants
Using arrays makes it a little less verbose and easier to manage IMO. SELECT v FROM unnest(array['a','b','c','d']) v Is that 8.4? or is unnest from contrib/ ? thanks! Dave Unnest is included in 8.4, but it's pretty much essential for working with arrays. Pre 8.4, you'd add the function like so CREATE OR REPLACE FUNCTION unnest(anyarray) RETURNS SETOF anyelement AS $BODY$ SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ LANGUAGE 'sql' IMMUTABLE STRICT -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] [Q] parsing out String array
if I have field declared
myvalue text[][]
insert into vladik (myval)
values
(
'{{"\",A", "\"B"}, {"Y", "Q"}}'
)
What do you guys use in your treasurechest of 'addons'
to successfully parse out the above trickery
and get
and get the 4 strings
",A
"B
Y
Q
from within Postgres stored procedure as well as C++ or other client
code.
It seems to me that it is not possible with any built-in command
available
to easily extract the strings out to a multidimensional array
thank you
--
Vlad P
author of C++ ORM http://github.com/vladp/CppOrm/tree/master
--
http://www.fastmail.fm - One of many happy users:
http://www.fastmail.fm/docs/quotes.html
--
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] trouble building pgbench on CentOS 5.3: /usr/bin/ld: cannot find -lpgport
Tom Lane escribió: > Aleksey Tsalolikhin writes: > > Hi. I am trying to build pgbench on CentOS 5.3 x86_64. > > make complains that it cannot find -lpgport > > > # cd contrib > > # make all > > You need to "make" the rest of the tree first. Or at least the > src/port/ part. Sounds like a makefile bug to me. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- 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] Simulate count result are distinct between 8.3 and 8.4
On Thu, Aug 13, 2009 at 9:37 AM, Chris wrote: > Emanuel Calvo Franco wrote: > >> > But in 8.4 throws this: >> >> postgres=# SELECT >> postgres-# (select count(i)+1 from prueba >> postgres(# where i < xi.i >> postgres(# ) as rownum, i, p >> postgres-# FROM prueba xi limit 5; >> rownum | i| p >> ++ >> 168770 | 168763 | 908731 >> 168771 | 168764 | 640826 >> 168772 | 168765 | 571112 >> 168773 | 168766 | 992462 >> 168774 | 168767 | 992471 >> (5 filas) >> > > > Why don't you make it simple and just use row_number() from 8.4... It can be simplified as: select row_number() over(), i, p from prueba limit 5; -- Shoaib Mir http://shoaibmir.wordpress.com/
Re: [GENERAL] Looping through string constants
On Wed, Aug 12, 2009 at 07:10:16PM -0400, Tom Lane wrote: - David Kerr writes: - > I'd like to loop through a group of constant string values using plpgsql - > The best analog i can think of would be in a shell script - > #!/usr/bin/ksh - - > for a in a b c d e; do - - Use VALUES? looks like that'll do it, thanks! Dave -- 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] trouble building pgbench on CentOS 5.3: /usr/bin/ld: cannot find -lpgport
On Wed, Aug 12, 2009 at 4:19 PM, Tom Lane wrote: > Aleksey Tsalolikhin writes: >> Hi. I am trying to build pgbench on CentOS 5.3 x86_64. >> make complains that it cannot find -lpgport > >> # cd contrib >> # make all > > You need to "make" the rest of the tree first. Or at least the > src/port/ part. Much better! Thank you, Tom! Best, Aleksey -- 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] Simulate count result are distinct between 8.3 and 8.4
Emanuel Calvo Franco wrote: hi people! I have this test query to simulate rownums in 8.3: SELECT (select count(i)+1 from prueba where i < xi.i ) as rownum, i, p FROM prueba xi limit 5; Devuelve , que esta bien: 1;1;"299361" 2;2;"421127" 3;3;"166284" 4;4;"458945" 5;5;"81619" But in 8.4 throws this: postgres=# SELECT postgres-# (select count(i)+1 from prueba postgres(# where i < xi.i postgres(# ) as rownum, i, p postgres-# FROM prueba xi limit 5; rownum | i| p ++ 168770 | 168763 | 908731 168771 | 168764 | 640826 168772 | 168765 | 571112 168773 | 168766 | 992462 168774 | 168767 | 992471 (5 filas) Without an order by in your query, the db can return them as soon as it finds the rows. If you add an order by (to the outer part), it should be more reliable. -- Postgresql & php tutorials http://www.designmagick.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] Looping through string constants
David Kerr wrote:
> I'd like to loop through a group of constant string values using plpgsql
> The best analog i can think of would be in a shell script
> #!/usr/bin/ksh
> for a in a b c d e; do
> echo $a
> done
> ./a.ksh
> a
> b
> c
> d
> e
> Is there some tricky way I can make that happen in postgres?
> (I don't want to put the values in a table =) that would be too easy!)
If you do not want to use arrays, you can always use:
| FOR r IN SELECT a FROM (VALUES ('a'), ('b'), ('c'), ('d'), ('e')) AS t(a)
ORDER BY a LOOP
| RAISE NOTICE '%', r.a;
| END LOOP;
Tim
--
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] trouble building pgbench on CentOS 5.3: /usr/bin/ld: cannot find -lpgport
Aleksey Tsalolikhin writes: > Hi. I am trying to build pgbench on CentOS 5.3 x86_64. > make complains that it cannot find -lpgport > # cd contrib > # make all You need to "make" the rest of the tree first. Or at least the src/port/ part. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Looping through string constants
David Kerr writes: > I'd like to loop through a group of constant string values using plpgsql > The best analog i can think of would be in a shell script > #!/usr/bin/ksh > for a in a b c d e; do Use VALUES? regression=# create function foo() returns int as $$ regression$# declare s int := 0; regression$# r record; regression$# begin regression$# for r in values (1),(2),(3),(4) loop regression$# s := s + r.column1; regression$# end loop; regression$# return s; regression$# end$$ language plpgsql; CREATE FUNCTION regression=# select foo(); foo - 10 (1 row) regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] trouble building pgbench on CentOS 5.3: /usr/bin/ld: cannot find -lpgport
Hi. I am trying to build pgbench on CentOS 5.3 x86_64. make complains that it cannot find -lpgport # cd contrib # make all ... make[1]: Entering directory `/home/tsalolia/postgresql-8.3.7/contrib/pgbench' gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv pgbench.o -L../../src/port -lpgport -L../../src/interfaces/libpq -lpq -L../../src/port -Wl,-rpath,'/usr/local/pgsql/lib' -lpgport -lz -lreadline -ltermcap -lcrypt -ldl -lm -o pgbench /usr/bin/ld: cannot find -lpgport collect2: ld returned 1 exit status make[1]: *** [pgbench] Error 1 make[1]: Leaving directory `/home/tsalolia/postgresql-8.3.7/contrib/pgbench' make: *** [all] Error 2 [r...@ddc-db-prod01 contrib]# ls $LD_LIBRARY_PATH adminpack.soeuc_tw_and_big5.so libecpg.so libpq.autf8_and_cyrillic.so utf8_and_iso8859_1.so ascii_and_mic.solatin2_and_win1250.so libecpg.so.6 libpq.so utf8_and_euc_cn.so utf8_and_iso8859.so cyrillic_and_mic.so latin_and_mic.so libecpg.so.6.0 libpq.so.5 utf8_and_euc_jis_2004.so utf8_and_johab.so dict_snowball.solibecpg.a libpgport.a libpq.so.5.1 utf8_and_euc_jp.so utf8_and_shift_jis_2004.so euc_cn_and_mic.so libecpg_compat.a libpgtypes.a pgxs utf8_and_euc_kr.so utf8_and_sjis.so euc_jis_2004_and_shift_jis_2004.so libecpg_compat.so libpgtypes.so plpgsql.so utf8_and_euc_tw.so utf8_and_uhc.so euc_jp_and_sjis.so libecpg_compat.so.3 libpgtypes.so.3utf8_and_ascii.so utf8_and_gb18030.so utf8_and_win.so euc_kr_and_mic.so libecpg_compat.so.3.0 libpgtypes.so.3.0 utf8_and_big5.so utf8_and_gbk.so # ls $LD_LIBRARY_PATH/*.a /usr/local/pgsql/lib/libecpg.a /usr/local/pgsql/lib/libpgport.a /usr/local/pgsql/lib/libpq.a /usr/local/pgsql/lib/libecpg_compat.a /usr/local/pgsql/lib/libpgtypes.a ]# How can I get this compile to go through, please? I've set up my LD_LIBRARY_PATH env var, and exported it, but still no luck... I imagine this is something very basic... Thanks, Aleksey -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Looping through string constants
I'd like to loop through a group of constant string values using plpgsql The best analog i can think of would be in a shell script #!/usr/bin/ksh for a in a b c d e; do echo $a done ./a.ksh a b c d e Is there some tricky way I can make that happen in postgres? (I don't want to put the values in a table =) that would be too easy!) Thanks Dave -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Simulate count result are distinct between 8.3 and 8.4
hi people! I have this test query to simulate rownums in 8.3: SELECT (select count(i)+1 from prueba where i < xi.i ) as rownum, i, p FROM prueba xi limit 5; Devuelve , que esta bien: 1;1;"299361" 2;2;"421127" 3;3;"166284" 4;4;"458945" 5;5;"81619" But in 8.4 throws this: postgres=# SELECT postgres-# (select count(i)+1 from prueba postgres(# where i < xi.i postgres(# ) as rownum, i, p postgres-# FROM prueba xi limit 5; rownum | i| p ++ 168770 | 168763 | 908731 168771 | 168764 | 640826 168772 | 168765 | 571112 168773 | 168766 | 992462 168774 | 168767 | 992471 (5 filas) synchronize_seqscan are on in both engines. I only want to know why is this difference... -- Emanuel Calvo Franco Database consultant at: www.siu.edu.ar www.emanuelcalvofranco.com.ar -- 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] comparing NEW and OLD (any good this way?)
Sam Mason wrote: > > But for rows, there is no such thing. You can't assign null to a row, it > > makes no sense and actually causes an error. > > What makes you say this? There's no reason I can see that would cause > row values should be special in this way. Maybe if you could define > what you mean by "you can't assign null to a row"? It seems to me that there is something special with rows: in tables, the values of columns may be null or not, but at the level of the row, there is no information that would say: this row itself as an object is null. Anyway, let's try to assign null to a row variable (with 8.4.0): CREATE TABLE our_table(i int); CREATE FUNCTION test() returns void as $$ declare r our_table; begin r:=null; end; $$ LANGUAGE plpgsql; SELECT test() yields: ERROR: cannot assign non-composite value to a row variable CONTEXT: PL/pgSQL function "test" line 4 at assignment As a follow-up to the comparison between rows and arrays, note that if we'd make r an int[], there would be no error. However, I agree that if we consider that a row is a composite type, then there is a problem because we sure can insert NULL into a column that is of a composite type. So the "row cannot be null" line of reasoning holds only so far as you don't stuff rows into columns :) Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] synchronous_commit and mvcc
Kelly Burkhart wrote: Hello, We have synchronous_commit=off in our postgresql.conf file. Does this setting affect mvcc? For instance if I have two connections from processes on different machines that do the following: c1 begins transaction c1 inserts rows into table c1 commits transaction c2 begins transaction c2 queries table At this point, is it possible that c2 is not able to see the rows from connection 1 that were inserted and committed prior to the c2 transaction starting? Thanks, -K > Does this setting affect mvcc? No, only how its saved to disk. Clients will see no difference. But transaction isolation can mess with it: http://www.postgresql.org/docs/8.3/static/transaction-iso.html -Andy -- 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] synchronous_commit and mvcc
On Wed, Aug 12, 2009 at 8:28 PM, Kelly Burkhart wrote: > Hello, > > We have synchronous_commit=off in our postgresql.conf file. Does this > setting affect mvcc? If you don't have a crash then there is absolutely no difference from the clients' point of view (besides speed). If you have a crash you could lose the last 200ms of commits but if that happens you'll lose them in a "consistent" way. You can't find the results of one transaction committed afterwards but not some other transaction which came earlier. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] synchronous_commit and mvcc
Hello, We have synchronous_commit=off in our postgresql.conf file. Does this setting affect mvcc? For instance if I have two connections from processes on different machines that do the following: c1 begins transaction c1 inserts rows into table c1 commits transaction c2 begins transaction c2 queries table At this point, is it possible that c2 is not able to see the rows from connection 1 that were inserted and committed prior to the c2 transaction starting? Thanks, -K
Re: [GENERAL] comparing NEW and OLD (any good this way?)
On Wed, Aug 12, 2009 at 08:02:10PM +0200, Daniel Verite wrote: >Sam Mason wrote: > > But it seems to be a somewhat arbitrary choice to handle > > IS NULL for rows differently from everything else. > > For scalar or array types, "is null" means that the value happens to be that > special value that we call null. No conceptual problem here. > But for rows, there is no such thing. You can't assign null to a row, it > makes no sense and actually causes an error. What makes you say this? There's no reason I can see that would cause row values should be special in this way. Maybe if you could define what you mean by "you can't assign null to a row"? > Starting from that point, what consistency can we expect for the "is null" > operator across row types and other types? Values of row type are the only time when v IS NOT NULL and NOT v IS NULL are not synonymous. > > Yes, I understand what it's specified to do and that it's consistent > > with SQL spec. I just think (and Merlin seems to agree) that the spec > > has specified the "wrong" behavior. > > So for you guys, what would be the "right" behavior? For me anyway, that the above actually holds true. -- Sam http://samason.me.uk/ -- 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] comparing NEW and OLD (any good this way?)
Sam Mason wrote: > But it seems to be a somewhat arbitrary choice to handle > IS NULL for rows differently from everything else. For scalar or array types, "is null" means that the value happens to be that special value that we call null. No conceptual problem here. But for rows, there is no such thing. You can't assign null to a row, it makes no sense and actually causes an error. Starting from that point, what consistency can we expect for the "is null" operator across row types and other types? > Yes, I understand what it's specified to do and that it's consistent > with SQL spec. I just think (and Merlin seems to agree) that the spec > has specified the "wrong" behavior. So for you guys, what would be the "right" behavior? Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] comparing NEW and OLD (any good this way?)
On Wed, Aug 12, 2009 at 04:14:31PM +0200, Daniel Verite wrote: > But IS NULL applied to an array is useless to test if there are null values > inside, whereas this is apparently the whole point of IS NULL applied to > rows. > I mean: > select a is null from (select array[null]) x(a); > returns false, as well as: > select a is null from (select array[1]) x(a); Yes, I know. But it seems to be a somewhat arbitrary choice to handle IS NULL for rows differently from everything else. > When applied to rows, if you consider that: > - is null applied to a row means that all columns are null > - is not null applied to a row means that all columns are not null > which is what the standard seems to dictate, then these operators make sense > and are probably useful in some situations. Yes, I understand what it's specified to do and that it's consistent with SQL spec. I just think (and Merlin seems to agree) that the spec has specified the "wrong" behavior. > Now there is the unfortunate consequence that (r is null) is not equivalent > to (not (r is not null)), yet it's not the standard's fault if "not all > values are null" is not the same as "all values are not null", that's just > set logic. Yes; but this means the user now has to be aware of exactly which type their code is using as the behavior of various things will magically change in rare circumstances. > Maybe they could have made this easier for us by naming the operators > differently, such as "is entirely null" and "is entirely not null" Yes, this would be *much* more preferable. For people aware of it this it's obviously an easy translation to make, but it's a nasty waiting for those who aren't and especially for anybody doing anything formal. I.e. when reasoning about operator semantics you suddenly have to know the type of data you're dealing with before you can say useful things about the result. There will of course be ways of avoiding the general case of an exponential increase in complexity, but it's still nasty. Anybody else think this thread is past it's bed time and should be put to rest? -- Sam http://samason.me.uk/ -- 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] comparing NEW and OLD (any good this way?)
On Wed, Aug 12, 2009 at 10:14 AM, Daniel Verite wrote: > Sam Mason wrote: > >> Nope, I still don't get it. Why treat rows specially? If this was >> true, then what should: >> >> SELECT a IS NULL, a IS NOT NULL >> FROM (SELECT ARRAY [1,NULL]) x(a); >> >> evaluate to? As "part of it" is NULL and part isn't then, by your >> reasoning, it should return TRUE for both. PG doesn't and I think this >> is much more useful behavior. > > But IS NULL applied to an array is useless to test if there are null values > inside, whereas this is apparently the whole point of IS NULL applied to > rows. > I mean: > select a is null from (select array[null]) x(a); > returns false, as well as: > select a is null from (select array[1]) x(a); > > When applied to rows, if you consider that: > - is null applied to a row means that all columns are null > - is not null applied to a row means that all columns are not null > which is what the standard seems to dictate, then these operators make sense > and are probably useful in some situations. > > Now there is the unfortunate consequence that (r is null) is not equivalent > to (not (r is not null)), yet it's not the standard's fault if "not all > values are null" is not the same as "all values are not null", that's just > set logic. > > Maybe they could have made this easier for us by naming the operators > differently, such as "is entirely null" and "is entirely not null" IMO, the standard really blew it. PostgreSQL's approach is ok, minimal standards compliance balanced out with practical considerations. This leads to some strange behaviors as noted upthread, but it's workable if you know the tricks. I guess it's not very well documented merlin -- 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] comparing NEW and OLD (any good this way?)
Sam Mason wrote: > Nope, I still don't get it. Why treat rows specially? If this was > true, then what should: > > SELECT a IS NULL, a IS NOT NULL > FROM (SELECT ARRAY [1,NULL]) x(a); > > evaluate to? As "part of it" is NULL and part isn't then, by your > reasoning, it should return TRUE for both. PG doesn't and I think this > is much more useful behavior. But IS NULL applied to an array is useless to test if there are null values inside, whereas this is apparently the whole point of IS NULL applied to rows. I mean: select a is null from (select array[null]) x(a); returns false, as well as: select a is null from (select array[1]) x(a); When applied to rows, if you consider that: - is null applied to a row means that all columns are null - is not null applied to a row means that all columns are not null which is what the standard seems to dictate, then these operators make sense and are probably useful in some situations. Now there is the unfortunate consequence that (r is null) is not equivalent to (not (r is not null)), yet it's not the standard's fault if "not all values are null" is not the same as "all values are not null", that's just set logic. Maybe they could have made this easier for us by naming the operators differently, such as "is entirely null" and "is entirely not null" Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] Best way to "mask" password in DBLINK
Magnus Hagander writes: >>> If I'm not mistaken, it's possible to put your password in the .pgpass >>> file in the postgres-users home folder, on the server where the postgres >>> cluster is running. > You need to put it in the .pgpass file of the postgres user - the one > that runs the server. .pgpass is dealt with by libpq, and DBLink and > DBI-Link both use libpq to connect to the remote server. Didn't we recently add a security fix to prevent non-superusers from relying on the server's .pgpass file? I think 8.4 provides a reasonable solution to this via the SQL/MED additions. In previous releases it's hard to find a nice place to keep the password for a dblink connection. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Adding ACL notion to existing tables
Le 08/08/09 02:13, Bruno Baguette a écrit : As you can see, I'm not really convinced for SOLUTION A or SOLUTION B as they have both some CONS. And they will both require some stored procedures to check references integrity. Solution B seems to be less weird to me. What would you do in that kind of situation ? Which solution would you take ? Would you uses another design to solve that problem ? Feel free to leave your appreciation about that problem. Your advices, tips or some interesting URLs are welcome also ! No opinion about theses 2 solutions ? :-/ Many thanks in advance for any advices ! Regards, -- Bruno Baguette -- 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] comparing NEW and OLD (any good this way?)
On Wed, Aug 12, 2009 at 10:51:04AM +0200, Willy-Bas Loos wrote: > > SELECT r IS NULL, r IS NOT NULL > > FROM (VALUES (1,NULL)) r(a,b); > > > > returns FALSE for *both* columns. How can a row be both NULL *and* > > non-NULL? > > Actually, the value is neither NULL, nor non-NULL. > Part of it is NULL and part of it isn't so neither "IS NULL" is true, > nor is "IS NOT NULL" Nope, I still don't get it. Why treat rows specially? If this was true, then what should: SELECT a IS NULL, a IS NOT NULL FROM (SELECT ARRAY [1,NULL]) x(a); evaluate to? As "part of it" is NULL and part isn't then, by your reasoning, it should return TRUE for both. PG doesn't and I think this is much more useful behavior. The value itself is not unknown, it just happens to contain some unknown values. Having a row that consists entirely of NULL values being treated as NULL is OK, but some weird halfway house is horrible. Standards' conforming, but still horrible. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] totally different plan when using partitions
query using partitions explicitly ("1"):
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73_0610_1 as data on
data.ne_id=cell_bsc.nome1
left outer join teststscell13_0610_1 as data1 on
data1.ne_id=cell_bsc.nome1 and data1.time=data.time
where
data.time >=cell_bsc.starttime and data.time <=cell_bsc.endtime and
data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00'
and data1.time >=cell_bsc.starttime and data1.time <=cell_bsc.endtime
anddata1.time between '2006-10-01 00:00:00' and '2006-10-06
00:00:00'
and cell_bsc.nome2=2
explain analyze:
http://explain-analyze.info/query_plans/3805-query-plan-2509
same query, but using postgresql's partition pruning ("2"):
explain analyze
select nome1,
thv3tralacc,
dltbfpgpdch
FROM cell_bsc_60_0610 as cell_bsc
left outer join teststscell73 as data on
data.ne_id=cell_bsc.nome1
left outer join teststscell13 as data1 on
data1.ne_id=cell_bsc.nome1 and data1.time=data.time
where
data.time >=cell_bsc.starttime and data.time <=cell_bsc.endtime and
data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00'
and data1.time >=cell_bsc.starttime and data1.time <=cell_bsc.endtime
anddata1.time between '2006-10-01 00:00:00' and '2006-10-06
00:00:00'
and cell_bsc.nome2=2
explain analyze:
http://explain-analyze.info/query_plans/3807-query-plan-2511
The second version is A LOT slower (10x). But the 2 queries should be
identical... why the two totally different plans???
As you can see in query "1" I just put the used table, in query "2" postgres
uses exactly the table I put in "1" (plus the empty tables that are the
"father" of the other tables); so I don't understand why the 2 plans...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plpython.dll dependencies
I note that plpython.dll references a specific Python version. In the case of Postgres 8.1.4, which is what I have installed, the reference is to Python24.dll. Is there yet a Postgres version in which plpython.dll references Python26.dll? (I am running on Windows XP Professional Service Pack 3.)
Re: [GENERAL] comparing NEW and OLD (any good this way?)
> SELECT r IS NULL, r IS NOT NULL > FROM (VALUES (1,NULL)) r(a,b); > > returns FALSE for *both* columns. How can a row be both NULL *and* > non-NULL? Actually, the value is neither NULL, nor non-NULL. Part of it is NULL and part of it isn't so neither "IS NULL" is true, nor is "IS NOT NULL" cheers, WBL On Wed, Jul 29, 2009 at 3:40 PM, Sam Mason wrote: > On Wed, Jul 29, 2009 at 01:15:27PM +, Jasen Betts wrote: >> On 2009-07-23, Sam Mason wrote: >> > >> > http://www.postgres.cz/index.php/PostgreSQL_SQL_Tricks#Attention_on_IS_NULL_and_IS_NOT_NULL_operators_for_composite_types >> > >> > is scary; even worse is that it was changed to be like this in 8.2 >> > because the standard says it should behave this way. What on earth were >> > they thinking when they defined the standard this way? >> >> since any comparson involving those tuples will return NULL true is the >> correct value for IS NULL > > I think you missed the point: > > SELECT r IS NULL, r IS NOT NULL > FROM (VALUES (1,NULL)) r(a,b); > > returns FALSE for *both* columns. How can a row be both NULL *and* > non-NULL? > >> if you are bothered by this behavior you are misusing NULL. > > I understand that this is the specified behavior, and hence PG is > correctly following the spec--but it still bothers me. > > -- > Sam http://samason.me.uk/ > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- "Patriotism is the conviction that your country is superior to all others because you were born in it." -- George Bernard Shaw -- 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] ERROR: XLogFlush: request AF/5703EDC8 is not satisfied --- flushed only to AF/50F15ABC
utsav.turray wrote: Dear All, I am using postgres 7.3 version on RHEL 4.0. My database has been restored. All tables all working fine i.e select , update but on a particular table its showing error ERROR: XLogFlush: request AF/5703EDC8 is not satisfied --- flushed only to AF/50F15ABC I have searched other threads, it shows the problem may be due to garbage in the LSN field of a page header. Now what is the solution to this problem. 1. Dump the database immediately so you have a backup. 2. Check that you can restore from that backup. 3. Make sure you are running the latest release of 7.3 - 7.3.21 available from ftp://ftp-archives.postgresql.org/ 4. Upgrade as soon as is practical 7.4 was released in 2003 and we are currently on 8.4 5. Test your hardware - disk errors might have caused this. If you are having problems dumping the database you might need to identify which table and set of records is giving you problems and exclude those. -- Richard Huxton Archonet Ltd -- 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] PQoidValue - isn't it for...?
On Wed, Aug 12, 2009 at 10:11, Jim Michaels wrote: > I am struggling to learn libpq. > > for some reason, I could not get an INSERT to produce an Oid. actually, By default, tables are created without Oids. > what I am looking for, is to get the ID of the last record inserted or to > verify that I inserted a record successfully. I think you use > PQresultStatus() for that.(?) If you want to get the ID, use something like: INSERT INTO ... VALUES (...) RETURNING id Assuming "id" is the name of your serial column. It will then return a regular resultset that you can access with PQgetvalue(). But, if you just want to know if the insert succeeded or not, the check of PQresultStatus() is enough. If that one returns PGRES_COMMAND_OK, it means the INSERT was ok. There is no need for your application to do any further verification. > Isn't PQoidValue() for getting the last INSERT id? or am I misunderstanding > it? It will get you the last oid, *if* the table has Oids, which user tables by default don't have. And in most cases, they shouldn't have - using a SERIAL or BIGSERIAL column is in most cases much better. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Best way to "mask" password in DBLINK
Ow Mun Heng wrote: -Original Message- From: Magnus Hagander [mailto:mag...@hagander.net] On Wed, Aug 12, 2009 at 09:30, Ow Mun Heng wrote: From: Tommy Gildseth [mailto:tommy.gilds...@usit.uio.no] Ow Mun Heng wrote: I'm starting to use DBLink / DBI-Link and one of the "bad" things is that the password is out in the clear. What can I do to prevent it from being such? How do I protect it from 'innocent' users? If I'm not mistaken, it's possible to put your password in the .pgpass file in the postgres-users home folder, on the server where the postgres cluster is running. Isn't that how one connects using the CLI? Eg: via psql? You need to put it in the .pgpass file of the postgres user - the one that runs the server. .pgpass is dealt with by libpq, and DBLink and DBI-Link both use libpq to connect to the remote server. The View is owned by the user "operator" not postgres Does it make a difference? My understanding of your words are that it _does_ make a difference and If I put it into the .pgpass of the postgres user then all is fine. No, it doesn't matter which role owns the database object. The system user trying to connect to the remote cluster via dblink, is the user which owns the postgres process, ie. normally the postgres system user. libpq will therefor look for the .pgpass file in the postgres system users home folder, irrespective of which role owns the database, or which role is used to connect to the database etc. -- Tommy Gildseth -- 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] Best way to "mask" password in DBLINK
-Original Message- >From: Magnus Hagander [mailto:mag...@hagander.net] >No, we're talking about operating system user here, not postgres user. >So the owner of the database object is irrelevant - only the user that >the backend process is executing as. Got it.. Thanks for the tip. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PQoidValue - isn't it for...?
I am struggling to learn libpq.
for some reason, I could not get an INSERT to produce an Oid. actually, what I
am looking for, is to get the ID of the last record inserted or to verify that
I inserted a record successfully. I think you use PQresultStatus() for that.(?)
Isn't PQoidValue() for getting the last INSERT id? or am I misunderstanding it?
I just figured out how to set the start id of a BIGSERIAL to 1000.
SAMPLE OUTPUT:
firstname:Horatio
middlename:P
lastname:Algers
homephone:345-678-9012
workphone:
cellphone:
pager:
company:
address1:
address2:
city:
mailstop:
stateprovince:
postalcode:
country:
comment:abc,123,456
website:
emailhome:
emailwork:
QUERY:"INSERT INTO
s_phonelist.phonelist(firstname,middlename,lastname,homephone,workphone,cellphone,pager,company,address1,address2,city,mailstop,stateprovi
,postalcode,country,_comment,website,emailhome,emailwork)
VALUES('Horatio','P','Algers','345-678-9012','','','','','','','','','','','','abc,123,456','','','')"
ERROR: INSERT operation failed!
done.
sprintf(querystr, "INSERT INTO s_phonelist.phonelist(%s)\n"
"VALUES(%s)", fnl, vl);
printf("QUERY:\"%s\"\n", querystr);
pgr = PQexec(pgc,querystr);
if (PGRES_COMMAND_OK!=PQresultStatus(pgr)) {
printf("INSERT result is not OK\n");
} else {
Oid oid = PQoidValue(pgr);
if (0 == oid) {
printf("ERROR: INSERT operation failed!\n");
}
}
Jim Michaels
Re: [GENERAL] Best way to "mask" password in DBLINK
On Wed, Aug 12, 2009 at 10:01, Ow Mun Heng wrote: > > > -Original Message- > From: Magnus Hagander [mailto:mag...@hagander.net] > On Wed, Aug 12, 2009 at 09:30, Ow Mun Heng wrote: >>> >>> From: Tommy Gildseth [mailto:tommy.gilds...@usit.uio.no] >>> >>> Ow Mun Heng wrote: > I'm starting to use DBLink / DBI-Link and one of the "bad" things is that > the password is out in the clear. > What can I do to prevent it from being such? How do I protect it from > 'innocent' users? >>> If I'm not mistaken, it's possible to put your password in the .pgpass file in the postgres-users home folder, on the server where the postgres cluster is running. >>> >>> Isn't that how one connects using the CLI? Eg: via psql? > >>You need to put it in the .pgpass file of the postgres user - the one >>that runs the server. .pgpass is dealt with by libpq, and DBLink and >>DBI-Link both use libpq to connect to the remote server. > > The View is owned by the user "operator" not postgres > Does it make a difference? No, we're talking about operating system user here, not postgres user. So the owner of the database object is irrelevant - only the user that the backend process is executing as. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Best way to "mask" password in DBLINK
-Original Message- From: Magnus Hagander [mailto:mag...@hagander.net] On Wed, Aug 12, 2009 at 09:30, Ow Mun Heng wrote: >> >> From: Tommy Gildseth [mailto:tommy.gilds...@usit.uio.no] >> >> Ow Mun Heng wrote: I'm starting to use DBLink / DBI-Link and one of the "bad" things is >>>that the password is out in the clear. What can I do to prevent it from being such? How do I protect it from 'innocent' users? >> >>>If I'm not mistaken, it's possible to put your password in the .pgpass >>>file in the postgres-users home folder, on the server where the postgres >>>cluster is running. >> >> Isn't that how one connects using the CLI? Eg: via psql? >You need to put it in the .pgpass file of the postgres user - the one >that runs the server. .pgpass is dealt with by libpq, and DBLink and >DBI-Link both use libpq to connect to the remote server. The View is owned by the user "operator" not postgres Does it make a difference? My understanding of your words are that it _does_ make a difference and If I put it into the .pgpass of the postgres user then all is fine. Thanks for confirmation -- 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] NOTICE: there is no transaction in progress
Rodrick Hales wrote: We have two machines that run a C application that interfaces with a Postgres database. They are our development and production machines. The version is PostgreSQL 8.3.7 on i686-redhat-linux-gnu, complied by GCC gcc (GCC) 4.1.2.20071124 (Red Hat 4.1.2-42) . On the development machine, I don't get notices and warnings related to Postgres SQL commands. COMMIT and ABORT are likely the culprits. On the production machine I do. The message you mention would be to a COMMIT/ROLLBACK being issued without a BEGIN. I think the setting you are after is probably client_min_messages (ch 18 of the manuals). One machine is probably set to NOTICE and one to WARNING. Some useful commands: SHOW client_min_messages; ALTER DATABASE db1 SET client_min_messages = 'WARNING'; ALTER USER u1 SET client_min_messages = ... -- Richard Huxton Archonet Ltd -- 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] Best way to "mask" password in DBLINK
On Wed, Aug 12, 2009 at 09:30, Ow Mun Heng wrote: > > > -Original Message- > From: Tommy Gildseth [mailto:tommy.gilds...@usit.uio.no] > > Ow Mun Heng wrote: >>> I'm starting to use DBLink / DBI-Link and one of the "bad" things is that >>> the password is out in the clear. >>> What can I do to prevent it from being such? How do I protect it from >>> 'innocent' users? > >>If I'm not mistaken, it's possible to put your password in the .pgpass >>file in the postgres-users home folder, on the server where the postgres >>cluster is running. > > Isn't that how one connects using the CLI? Eg: via psql? You need to put it in the .pgpass file of the postgres user - the one that runs the server. .pgpass is dealt with by libpq, and DBLink and DBI-Link both use libpq to connect to the remote server. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Best way to "mask" password in DBLINK
Ow Mun Heng wrote:
-Original Message-
From: Tommy Gildseth [mailto:tommy.gilds...@usit.uio.no]
Ow Mun Heng wrote:
I'm starting to use DBLink / DBI-Link and one of the "bad" things is that
the password is out in the clear.
What can I do to prevent it from being such? How do I protect it from
'innocent' users?
If I'm not mistaken, it's possible to put your password in the .pgpass
file in the postgres-users home folder, on the server where the postgres
cluster is running.
Isn't that how one connects using the CLI? Eg: via psql?
My connection string looks like this.
SELECT aaa
FROM dblink('dbname=hmxmms host=xxx.xxx.xxx.xxx user=yyy
password=zzz'::text, 'SELECT * from tablename'::text) b4(aaa xxx );
I've placed the above as a view hence the user/pass is being hardcoded(?) of
sorts
Just leave out the "password=zzz" part of the connection string.
--
Tommy Gildseth
--
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] Best way to "mask" password in DBLINK
-Original Message-
From: Tommy Gildseth [mailto:tommy.gilds...@usit.uio.no]
Ow Mun Heng wrote:
>> I'm starting to use DBLink / DBI-Link and one of the "bad" things is that
>> the password is out in the clear.
>> What can I do to prevent it from being such? How do I protect it from
>> 'innocent' users?
>If I'm not mistaken, it's possible to put your password in the .pgpass
>file in the postgres-users home folder, on the server where the postgres
>cluster is running.
Isn't that how one connects using the CLI? Eg: via psql?
My connection string looks like this.
SELECT aaa
FROM dblink('dbname=hmxmms host=xxx.xxx.xxx.xxx user=yyy
password=zzz'::text, 'SELECT * from tablename'::text) b4(aaa xxx );
I've placed the above as a view hence the user/pass is being hardcoded(?) of
sorts
--
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] Best way to "mask" password in DBLINK
Ow Mun Heng wrote: I'm starting to use DBLink / DBI-Link and one of the "bad" things is that the password is out in the clear. What can I do to prevent it from being such? How do I protect it from 'innocent' users? If I'm not mistaken, it's possible to put your password in the .pgpass file in the postgres-users home folder, on the server where the postgres cluster is running. -- Tommy Gildseth -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
