Re: [GENERAL] Build postgresql
Hello postgresql team! I have question about building postgresql. And here it is: how to build postgresql statically on windows to get static libs? Thanks
[GENERAL] Build postgresql
Hello postgresql team! I have question about building postgresql. And here it is: how to build postgresql statically on windows to get static libs? Thanks
Re: [GENERAL] How to quote the COALESCE function?
Tom, Jerry, I'm going to do the same as the `quote_identifier`
function of Postgres does, only quote if necessary.
Thanks for your explanation, Roman.
On Tue, Mar 29, 2016 at 1:31 AM, Jerry Sievers <gsiever...@comcast.net>
wrote:
> Roman Scherer <ro...@burningswell.com> writes:
>
> > Hello,
> >
> > I'm building a DSL in Clojure for SQL and specifically PostgreSQL
> > [1]. When building a SQL statement that contains a function call
> > I always quote the function name with \" in case the function
> > name contains any special characters. Here's an example:
> >
> > (select db ['(upper "x")])
> > ;=> ["SELECT \"upper\"(?)" "x"]
> >
> > This worked fine so far, but today I found a case that doesn't
> > work as expected, the COALESCE function.
> >
> > (select db ['(coalesce nil 0)])
> > ;=> ["SELECT \"coalesce\"(NULL, 0)"]
> >
> > Can someone explain to me what's the difference between quoting
> > the `upper` and the `coalesce` function? I can execute the
> > following statements via psql, and it works as expected:
> >
> > SELECT upper ('x');
> > SELECT "upper"('x');
> > SELECT coalesce(NULL, 1);
> >
> > But as soon as I try this with `coalesce` I get an error:
> >
> > SELECT "coalesce"(NULL, 1);
>
>
> While not a precise answer to your question, it may be of interest to
> note that coalesce is *not* a function.
>
> It is a language construct with a function-like syntax.
>
> select distinct proname from pg_proc where proname in ('coalesce',
> 'lower');
> proname
> -
> lower
> (1 row)
>
> >
> > ERROR: function coalesce(unknown, integer) does not exist
> > LINE 1: SELECT "coalesce"(NULL, 1);
> > ^
> > HINT: No function matches the given name and argument types. You
> might need to add explicit type casts.
> >
> > What I found so far is, that the `upper` function can be found in
> > the `pg_proc` table but not `coalesce`.
> >
> > SELECT proname FROM pg_proc WHERE proname ILIKE 'upper';
> > SELECT proname FROM pg_proc WHERE proname ILIKE 'coalesce';
> >
> > Does this mean that `coalesce` isn't a classical function and I
> > shouldn't quote it? Is it instead a keyword, as described in
> > the "Lexical Structure" section of the docs [2]? How can I find
> > out which other functions are not meant to be quoted?
> >
> > I'm aware that I do not need to quote the `coalesce` and `upper`
> > functions and I may change my strategy for quoting functions names.
> >
> > Thanks for you help, Roman.
> >
> > [1] https://github.com/r0man/sqlingvo
> > [2]
> http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html
> >
>
> --
> Jerry Sievers
> Postgres DBA/Development Consulting
> e: postgres.consult...@comcast.net
> p: 312.241.7800
>
[GENERAL] How to quote the COALESCE function?
Hello,
I'm building a DSL in Clojure for SQL and specifically PostgreSQL
[1]. When building a SQL statement that contains a function call
I always quote the function name with \" in case the function
name contains any special characters. Here's an example:
(select db ['(upper "x")])
;=> ["SELECT \"upper\"(?)" "x"]
This worked fine so far, but today I found a case that doesn't
work as expected, the COALESCE function.
(select db ['(coalesce nil 0)])
;=> ["SELECT \"coalesce\"(NULL, 0)"]
Can someone explain to me what's the difference between quoting
the `upper` and the `coalesce` function? I can execute the
following statements via psql, and it works as expected:
SELECT upper ('x');
SELECT "upper"('x');
SELECT coalesce(NULL, 1);
But as soon as I try this with `coalesce` I get an error:
SELECT "coalesce"(NULL, 1);
ERROR: function coalesce(unknown, integer) does not exist
LINE 1: SELECT "coalesce"(NULL, 1);
^
HINT: No function matches the given name and argument types. You might
need to add explicit type casts.
What I found so far is, that the `upper` function can be found in
the `pg_proc` table but not `coalesce`.
SELECT proname FROM pg_proc WHERE proname ILIKE 'upper';
SELECT proname FROM pg_proc WHERE proname ILIKE 'coalesce';
Does this mean that `coalesce` isn't a classical function and I
shouldn't quote it? Is it instead a keyword, as described in
the "Lexical Structure" section of the docs [2]? How can I find
out which other functions are not meant to be quoted?
I'm aware that I do not need to quote the `coalesce` and `upper`
functions and I may change my strategy for quoting functions names.
Thanks for you help, Roman.
[1] https://github.com/r0man/sqlingvo
[2] http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html
[GENERAL] copy from .. How to get rid of encoding check for bytea coumns
I am trying to load data into a rather simple table: CREATE TABLE public.files ( id SERIAL, idchar CHAR(32) NOT NULL, content BYTEA, CONSTRAINT files_pkey PRIMARY KEY(id) ) WITHOUT OIDS; with this command: copy files (idchar, content) from '/data/1.dat' delimiter '|'; The database encoding is UTF-8. Here is an example of the data file content: 0C2CCE6941194369B02B616F1301|\xFF\xD8\xFF\xE0\x00\x10\x4A\x46\x49\x46\x00\x01 And I get this error: ERROR: invalid byte sequence for encoding UTF8: 0xff The command set client_encoding = 'SQL_ASCII'; Does not helps at all, the result is the same error message. How can I turn off that annoying codepage checking during COPY FROM ? Isn't it a bug ? In my humble opinion, tt definitely should not check the encoding for columns of type bytea. Cheers, R.G. Disclaimer: http://www.aps-holding.com/disclaimer.html
[GENERAL] Are stored procedures always kept in memory?
We run several instances of postgre in different countries, and we try keeping them as same as possible, in terms of structure of the tables and function definitions (except the content of schema config, which differs between dbs). So if we need to implement some different algorithm per country, then we define a plpgsql function like this into each of our dbs: BEGIN select value from config.strings into country where name = 'country'; if country = 'CZ' then -- Some computations here elseif country = 'PL' then -- Different calculations here elseif country = 'RO' then -- Yet another algorithm here end if; return (result); END; In this function, we get the value from a table config.strings (which contains a different value in each country's database), and based on this value we go through a specific if-branch. Simple. But reading this configuration value may involve reading from a disk. So to avoid accessing the disk to fetch the country value, I would like to replace it by calling a function defined like this (in each db returning a different string indicating the country where db resides, of course): create or replace function config.country () returns char(3) as $$ select 'CZ'::char(3) $$ language sql immutable; And then call it like: if config.country () = 'CZ' then -- Some computations here Now my questions is: Are the stored functions (both plpgsql and plain sql functions) kept always in a memory? Or they are stored similarly like tables, on the disk, reading them into memory when called and possibly release them from memory, if memory is needed for something else? Thanks for reply. R.G. Disclaimer: http://www.aps-holding.com/disclaimer.html
[GENERAL] Postgres service refuses to start on windows
hello ! I having an issue with my postgres database installed on a windows xp. For some reason, I can no longer start the postgres service through pgadmin or the services window. I get the following error: Error 1053: The service did not respond to the start or control request in a timely fashion. Any ideas ? Roman.
[GENERAL] Deadlock on the same select for update
Hi, I have problem with deadlocks and don't know why it happens. Below is the log (postgres 9.0, debian): [11882]DETAIL: Process 11882 waits for ShareLock on transaction 44324308; blocked by process 11884. Process 11884 waits for ShareLock on transaction 44324307; blocked by process 11882. Process 11882: SELECT * FROM teddy WHERE id IN (112747007,112747008,112747011,112747013,112747015,112747016,112747020,112747021,112747022,112747024,112747025,112747028,112747030,112747032,112747034,112747035,112747038,112747043,112747044,112747045,112747050,112747052,112747053) FOR UPDATE Process 11884: SELECT * FROM teddy WHERE id IN (112747007,112747008,112747011,112747013,112747015,112747016,112747020,112747021,112747022,112747024,112747025,112747028,112747030,112747032,112747034,112747035,112747038,112747043,112747044,112747045,112747050,112747052,112747053) FOR UPDATE Regards, Roman -- 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] set-level update fails with unique constraint violation
# jayadevan.maym...@ibsplc.com / 2010-01-04 10:03:29 +0530: From: neuhauser+pgsql-general#postgresql@sigpipe.cz this fails with duplicate key value: CREATE TABLE x ( i INT NOT NULL UNIQUE ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; are there any plans to make this work? Hi, This seems to work.. UPDATE x set i=i+1 from (select i as m from x order by m desc) y where x.i = y.m Jayadevan Thanks, that nicely achieves the illusion of atomic immediate checking. -- Roman Neuhauser -- 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] set-level update fails with unique constraint violation
# scott.marl...@gmail.com / 2010-01-02 11:23:24 -0700: On Sat, Jan 2, 2010 at 1:40 AM, Roman Neuhauser neuhau...@sigpipe.cz wrote: # da...@fetter.org / 2009-12-31 08:04:58 -0800: On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql@sigpipe.cz wrote: Hello, this fails with duplicate key value: CREATE TABLE x ( i INT NOT NULL UNIQUE ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; are there any plans to make this work? This will work in 8.5: CREATE TABLE x ( i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; thanks, this might be a bearable workaround in some cases provided there's also SET CONSTRAINTS ... DEFERRED / IMMEDIATE. what I really want is a mode that fires the constraint check at the end of the statement. What advantage would there be to a constraint that fires right after to one that fires at the end of the transaction? What? I didn't say that. I'm saying that I want IMMEDIATE constraint that is atomic with regard to the statement. It's obvious that UPDATE x SET i = i + 1 cannot break a UNIQUE constraint on x.i lest the constraint checking is not atomic. I can see how such non-atomic checking can be good performance-wise, but I'm more interested in logical correctness. -- Roman Neuhauser -- 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] set-level update fails with unique constraint violation
# da...@fetter.org / 2009-12-31 08:04:58 -0800: On Thu, Dec 31, 2009 at 10:52:20AM +0100, neuhauser+pgsql-general#postgresql@sigpipe.cz wrote: Hello, this fails with duplicate key value: CREATE TABLE x ( i INT NOT NULL UNIQUE ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; are there any plans to make this work? This will work in 8.5: CREATE TABLE x ( i int NOT NULL UNIQUE DEFERRABLE INITIALLY DEFERRED ); INSERT INTO x (i) VALUES (1), (2), (3); UPDATE x SET i = i + 1; thanks, this might be a bearable workaround in some cases provided there's also SET CONSTRAINTS ... DEFERRED / IMMEDIATE. what I really want is a mode that fires the constraint check at the end of the statement. -- Roman Neuhauser -- 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] Emal reg expression
On Wed, Oct 28, 2009 at 05:45:14AM -0700, Xai wrote: i want to create a type for an email field but i'm not good with regx can some one help me? http://marc.info/?l=postgresql-generalm=112612299412819w=2 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 'create conversion' problem
Hello!
There is a database in KOI8-R encoding. And we have a client who is
querying the database:
set client_encoding TO 'ALT'
and then he write some data into the database.
I have a problem with some symbols which exists in ALT encoding and
which are absent in KOI8-R encoding. As result, during inserting strings
with such symbols postgresql returns an error, for example:
ERROR: character 0xfc of encoding ALT has no equivalent in MULE_INTERNAL
I've decided to write my own converter, so here is my code:
[code]
#include postgres.h
#include fmgr.h
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
PG_FUNCTION_INFO_V1(fix_alt_to_koi8r);
PG_FUNCTION_INFO_V1(fix_koi8r_to_alt);
Datum
fix_alt_to_koi8r(PG_FUNCTION_ARGS)
{
elog(NOTICE,ALT_TO_KOI8);
unsigned char *src = PG_GETARG_CSTRING(*2*);
unsigned char *dest = PG_GETARG_CSTRING(*3*);
int len = PG_GETARG_INT32(*4*);
Assert(PG_GETARG_INT32(*0*) == PG_ALT);
Assert(PG_GETARG_INT32(*1*) == PG_KOI8R);
Assert(len = *0*);
static const unsigned char convert866toKOI8[] = {
*0*,*1*,*2*,*3*,*4*,*5*,*6*,*7*,*8*,*9*,*10*,*11*,*12*,*13*,*14*,*15*,
*16*,*17*,*18*,*19*,*20*,*21*,*22*,*23*,*24*,*25*,*26*,*27*,*28*,*29*,*30*,*31*,
*32*,*33*,*34*,*35*,*36*,*37*,*38*,*39*,*40*,*41*,*42*,*43*,*44*,*45*,*46*,*47*,
*48*,*49*,*50*,*51*,*52*,*53*,*54*,*55*,*56*,*57*,*58*,*59*,*60*,*61*,*62*,*63*,
*64*,*65*,*66*,*67*,*68*,*69*,*70*,*71*,*72*,*73*,*74*,*75*,*76*,*77*,*78*,*79*,
*80*,*81*,*82*,*83*,*84*,*85*,*86*,*87*,*88*,*89*,*90*,*91*,*92*,*93*,*94*,*95*,
*96*,*97*,*98*,*99*,*100*,*101*,*102*,*103*,*104*,*105*,*106*,*107*,*108*,*109*,*110*,*111*,
*112*,*113*,*114*,*115*,*116*,*117*,*118*,*119*,*120*,*121*,*122*,*123*,*124*,*125*,*126*,*127*,
*225*,*226*,*247*,*231*,*228*,*229*,*246*,*250*,*233*,*234*,*235*,*236*,*237*,*238*,*239*,*240*,
*242*,*243*,*244*,*245*,*230*,*232*,*227*,*254*,*251*,*253*,*255*,*249*,*248*,*252*,*224*,*241*,
*193*,*194*,*215*,*199*,*196*,*197*,*214*,*218*,*201*,*202*,*203*,*204*,*205*,*206*,*207*,*208*,
*210*,*211*,*212*,*213*,*198*,*200*,*195*,*222*,*219*,*221*,*223*,*217*,*216*,*220*,*192*,*209*,
*180*,*164*,*183*,*167*,*196*,*197*,*198*,*199*,*200*,*201*,*202*,*203*,*204*,*205*,*206*,*207*,
*208*,*209*,*210*,*211*,*212*,*213*,*214*,*215*,*216*,*217*,*218*,*219*,*220*,*221*,*222*,*223*,
*210*,*211*,*212*,*213*,*198*,*200*,*195*,*222*,*219*,*221*,*223*,*217*,*216*,*220*,*192*,*209*,
*179*,*241*,*242*,*243*,*244*,*245*,*246*,*247*,*248*,*249*,*250*,*251*,*252*,*253*,*254*,*255*
};
while(len){
*dest = convert866toKOI8[(unsigned char)(*src)];
++src;
++dest;
--len;
}
PG_RETURN_VOID();
}
Datum
fix_koi8r_to_alt(PG_FUNCTION_ARGS)
{
elog(NOTICE,KOI8_TO_ALT);
unsigned char *src = PG_GETARG_CSTRING(*2*);
unsigned char *dest = PG_GETARG_CSTRING(*3*);
int len = PG_GETARG_INT32(*4*);
Assert(PG_GETARG_INT32(*0*) == PG_KOI8R);
Assert(PG_GETARG_INT32(*1*) == PG_ALT);
Assert(len = *0*);
static const unsigned char KOI8to866[] = {
*0*,*1*,*2*,*3*,*4*,*5*,*6*,*7*,*8*,*9*,*10*,*11*,*12*,*13*,*14*,*15*,
*16*,*17*,*18*,*19*,*20*,*21*,*22*,*23*,*24*,*25*,*26*,*27*,*28*,*29*,*30*,*31*,
*32*,*33*,*34*,*35*,*36*,*37*,*38*,*39*,*40*,*41*,*42*,*43*,*44*,*45*,*46*,*47*,
*48*,*49*,*50*,*51*,*52*,*53*,*54*,*55*,*56*,*57*,*58*,*59*,*60*,*61*,*62*,*63*,
*64*,*65*,*66*,*67*,*68*,*69*,*70*,*71*,*72*,*73*,*74*,*75*,*76*,*77*,*78*,*79*,
*80*,*81*,*82*,*83*,*84*,*85*,*86*,*87*,*88*,*89*,*90*,*91*,*92*,*93*,*94*,*95*,
*96*,*97*,*98*,*99*,*100*,*101*,*102*,*103*,*104*,*105*,*106*,*107*,*108*,*109*,*110*,*111*,
*112*,*113*,*114*,*115*,*116*,*117*,*118*,*119*,*120*,*121*,*122*,*123*,*124*,*125*,*126*,*255*,
*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,
*32*,*32*,*32*,*32*,*32*,*249*,*32*,*32*,*32*,*32*,*32*,*32*,*248*,*32*,*32*,*32*,
*32*,*32*,*32*,*241*,*193*,*32*,*105*,*195*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,*244*,
*32*,*32*,*32*,*240*,*192*,*32*,*73*,*194*,*252*,*32*,*32*,*32*,*32*,*32*,*32*,*32*,
*238*,*160*,*161*,*230*,*164*,*165*,*228*,*163*,*229*,*168*,*169*,*170*,*171*,*172*,*173*,*174*,
*175*,*239*,*224*,*225*,*226*,*227*,*166*,*162*,*236*,*235*,*167*,*232*,*237*,*233*,*231*,*234*,
*158*,*128*,*129*,*150*,*132*,*133*,*148*,*131*,*149*,*136*,*137*,*138*,*139*,*140*,*141*,*142*,
Re: [GENERAL] recommendations for reducing mem usage on local dev machine
# [EMAIL PROTECTED] / 2007-04-14 13:27:33 +0200: Hi, I am stuck for the moment with 1gig of ram on a win xp machine running a 8.2.3 postgres. With the java website taking 300meg, how is it going to scale? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Stored procedure
# [EMAIL PROTECTED] / 2007-03-13 18:29:36 +0100:
Hi,
I have a stored procedure which returns a SETOF RECORD.
so basically a partial rowtype from a table.
to execute the query in PHP, i must write :
select * from myschema.sp_a_002('username') as result(Column1 varchar);
to get the result.
However, is there another to get the result without using 'as result(column1
varchar)' ?
something like a simple select * from storedprocedure(param);, for example
:-)
Yes, create a type, and have the function return SETOF your-type. You
need to name the types *somewhere*.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Can you specify the pg_xlog location from a config file?
# [EMAIL PROTECTED] / 2007-01-26 09:21:27 -0800: Windows doesn't support symlinks. Is it possible instead for there to be a config file that lets one set where the pg_xlog directory will sit? Windows has junction points. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Installing PostgreSQL under Cpanel
# [EMAIL PROTECTED] / 2007-01-23 23:17:31 +0800: Please don't top-post, it disturbes the flow of the communication. Interesting. I prefer getting to the point an author is making. Top posting means you end up far off the mark. There's documentation about that too, at the end of the installation instructions IIRC. I did not see them. Please point me? I'd deeply appreciate it. Please stop asking for spoonfeeding. You have no problems reading email, how about some web pages? http://www.postgresql.org/docs/8.2/interactive/index.html http://www.postgresql.org/docs/8.2/interactive/bookindex.html -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] first steps in PhP and PostgreSQL
# [EMAIL PROTECTED] / 2006-11-06 15:05:25 +0100:
I'm sure that it's a typo or something, but as I'm getting into PhP
and PostgreSQL for the first time, I can't be sure.
?php
pg_connect (dbname=cdi user=cdi password=toto) or die
(Couldn't Connect: .pg_last_error());
$query=SELECT * FROM stock;
$query=pg_query($query);
// start the output
while($row=pg_fetch_array($query,NULL,PGSQL_ASSOC)) {
echo Title: .$row['isbn_no'].br /;
echo blah .$row['code_livre'].br /;
}
?
I copy that file to my apache server, in php_experimental/base.php
and access it via a browser.
I don't get an error message. I instead get a blank page.
Check apache's error log. If you can't figure it out then, better
place to ask further questions would be [EMAIL PROTECTED]
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Dump all databases to corresponding files
# [EMAIL PROTECTED] / 2006-11-06 12:26:43 +0100: On Sun, 05 Nov 2006, CSN wrote: Anybody know of a script that dumps all databases into corresponding dump files I've written this one in bash: # #!/bin/bash pg_dumpall -g /var/lib/pgsql/backups/globals.sql for dbname in `psql -qXtc select datname from pg_catalog.pg_database where datname'template0' template1` do pg_dump -b -F t $dbname /var/lib/pgsql/backups/$dbname.dump done # This would break if any database name has white space. Why don't you use while then? psql -qXtc $query template1 | while read dbname; do pg_dump -b -F t $dbname /var/lib/pgsql/backups/$dbname.dump done -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] more anti-postgresql FUD
# [EMAIL PROTECTED] / 2006-10-10 14:16:19 -0400: FUD from another open source project is really poor form, particulary when not in competing segements where a little bit of competitive rivalry is expected. OMG WTF what FUD??? # [EMAIL PROTECTED] / 2006-10-10 13:55:57 -0400: http://www.zabbix.com/manual/v1.1/install.php recent benchmarks using ZABBIX clearly show that PostgreSQL (7.1.x) is at least 10 times slower than MySQL (3.23.29) Note: These results are predictable. ZABBIX server processes use simple SQL statements like single row INSERT, UPDATE and simple SELECT operators. In such environment, use of advanced SQL engine (like PostgreSQL) is overkill. That's true. * no need to constantly run resource-hungry command vacuum for MySQL Last time I used MySQL that was true. Some time ago I did a simplistic, but quite telling, test. I had a large (several milion rows), indexed table, same data, in MySQL (4.0.x) and PostgreSQL (late 7.4), on the same RHEL or FreeBSD (don't remember) machine. Walking over the table with SELECT * FROM TABLE ORDER BY pk LIMIT 10 OFFSET N; or the MySQL equivalent, MySQL was several times faster than PostgreSQL, but the times were getting longer and longer As N grew in increments of 10, it took ages for MySQL to return the rows. PostgreSQL... Well, it was as slow with N=10 as it was with N=0. * MySQL is used as a primary development platform. How does *this* qualify as FUD? Or are *you* spreading FUD to scare people from even mentioning the software? -- I don't like MySQL. I hate it when people put cheerleading where reason should prevail. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Postgres Team: Thank You All
# [EMAIL PROTECTED] / 2006-09-20 21:59:10 -0400: To all involved in this project, I justed wanted to let you know how impressed and pleased I have been with postgres over the past 5 years . Remember, this is an opens source project. Satisfied users are similar to random victims in drive-by shootings. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] vista
# [EMAIL PROTECTED] / 2006-09-19 21:26:16 +1000: if you want to be taken seriously by anyone who uses Windows (hands up anyone who knows a Windows user) 1. what do those two things have in common? 2. what makes you think that anyone who uses Windows runs PostgreSQL on it? 3. my guess is you're a Windows programmer, and thus in much better position to fix the issue than Tom RedHat Lane. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Trigger (Transaction related)
# [EMAIL PROTECTED] / 2006-09-01 03:19:03 -0400: If that is the case then why does it throw error in one on the insert queries in the shared object written in SPI without inserting the row on the table on which record is inserted. Follwing query in the shared object throws an error. INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email FROM parser WHERE id = 1) Error is ERROR: syntax error at or near SELECT at character 53 Because there's a syntax error. test=# INSERT INTO headers (id, header_content) VALUES (1, SELECT raw_email FROM parser WHERE id = 1); ERROR: syntax error at or near SELECT at character 53 LINE 1: ...SERT INTO headers (id, header_content) VALUES (1, SELECT raw... ^ test=# INSERT INTO headers (id, header_content) VALUES (1, (SELECT raw_email FROM parser WHERE id = 1)); ERROR: relation headers does not exist test=# Regardless the error in the shared object, why doesn't it insert the row in the table on which AFTER INSERT trigger is written? Perhaps you should verify the syntax of your queries in a less demanding environment, like psql, first. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Create user or role from inside a function?
# [EMAIL PROTECTED] / 2006-09-01 20:13:14 +1000:
Hey,
I am running PostgreSQL 8.1.4 and I want to create a user from inside a
function. Is this possible in 8.1?
Ive found quite a few references on google using EXECUTE, but this seems
relevant to earlier versions, not 8.1.
I have a function like this:
CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar) RETURNS
VARCHAR LANGUAGE plpgsql AS '
BEGIN
EXECUTE CREATE USER || un || WITH PASSWORD || pw;
RETURN un;
END
';
Double quotes can be used only for identifiers (table, column
names, etc), you want to use single quotes for strings. Now, since
you already are in a string (the function body), you need to escape
the single quotes inside. In SQL this is done by doubling the quote
character, IOW, by putting another single quote just before it:
CREATE FUNCTION foo() ...
AS '
BEGIN
EXECUTE ''CREATE USER '' || un || '' WITH PASSWORD '' || pw;
RETURN un;
END
';
That assumes that the un and pw parameters are always passed already
quoted, otherwise you'll get errors like this:
test=# CREATE FUNCTION user_create (un varchar, uid bigint, pw varchar)
RETURNS
test-# VARCHAR LANGUAGE plpgsql AS '
test'# BEGIN
test'# EXECUTE ''CREATE USER '' || un || '' WITH PASSWORD '' || pw;
test'# RETURN un;
test'# END
test'# ';
CREATE FUNCTION
test=# select user_create('fubar', 0, 'pass');
ERROR: syntax error at or near pass at character 33
QUERY: CREATE USER fubar WITH PASSWORD pass
CONTEXT: PL/pgSQL function user_create line 2 at execute statement
LINE 1: CREATE USER fubar WITH PASSWORD pass
^
test=#
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] UUID as primary key
# [EMAIL PROTECTED] / 2006-08-31 07:35:30 -0700: Ralf Engelschall's OSSP uuid looks very good. Written in C with interfaces into PostgreSQL, PHP and C++ (classes wrapping the C structures and functions). http://www.ossp.org/pkg/lib/uuid/ Can you give a link to the PostgreSQL binding? I haven't been able to find it there. All I see is C, Perl and PHP bindings for the generation of UUIDs, and nothing about storage. The above page links to the source repository [1] which in turn contains a pgsql directory[2]. [1] http://cvs.ossp.org/dir?d=ossp-pkg/uuid [2] http://cvs.ossp.org/dir?d=ossp-pkg/uuid/pgsql CREATE TABLE t (id UUID DEFAULT 'uuid(4)' PRIMARY KEY); (For my application, as it happens, I don't need to generate UUIDs in the database, but I recognize that would be useful.) Actually, serverside generation of uuids is the least interesting aspect of that snippet. Use of custom types in indexes requires appropriate operator classes, and I was trying to give a hint that the library's support for PostgreSQL is quite comprehensive. Now, an operator class is no rocket science, but the ability to use the type in any way without having to add missing features is nice anyway. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Fwd: How to convert a string to bytea?
# [EMAIL PROTECTED] / 2006-08-30 12:22:47 -0400: -- Forwarded message -- From: Chris Hoover [EMAIL PROTECTED] Date: Aug 30, 2006 12:22 PM Subject: How to convert a string to bytea? To: pgsql-admin@postgresql.org pgsql-admin@postgresql.org I am in need of some help. I need to use the encode function against a text string in one of my pg/plsql functions. However, the encode function expects a bytea field to be passed to it. How do I convert my text string into a bytea? See decode()/encode() in http://www.postgresql.org/docs/8.1/static/functions-binarystring.html -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plz unsubscribe me
# [EMAIL PROTECTED] / 2006-08-30 14:11:11 -0400: Alvaro Herrera [EMAIL PROTECTED] writes: So I don't think we should do anything about it. It's not really a serious problem. Actually, what I'd like to see done is to get majordomo to bounce list messages containing unsubscribe in the subject, with an explanatory message about the right way to unsubscribe. There's no reason the rest of us should be bothered. Please at least make it at least /^subject:\s*unsubscribe\s*$/i so it doesn't bounce valid mail. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plz unsubscribe me
# [EMAIL PROTECTED] / 2006-08-30 12:32:21 -0500: There are good reasons why that is not a feature found in many popular email clients. The biggest of which is that if it was people would use it all the time and spammers would abuse it as a way to cull current email addresses. IOW getting rid of the web subscription form would probably cut a nice dent into the Subject: unsubscribe heap. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] UUID as primary key
# [EMAIL PROTECTED] / 2006-08-21 21:16:14 -0700: I'm considering using a UUID as a primary / foreign key for my schema, to help ensure portability of data in a multi-master context. Does anyone have experience with this? There's a project on Gborg (pguuid) to create a native UUID type, but it looks stagnant (and I'd prefer to use PostgreSQL out of the box, if I can). So I'm considering three possible representations: * VARCHAR(36) or CHAR(36) containing the standard textual encoding * NUMERIC(40,0) containing the 128-bit binary version of the UUID, considered as an integer * A pair of BIGINT columns, containing the two 64-bit halves of the binary UUID, handled together as a two-column key. Would any of these give reasonable performance (for joins of large tables), compared to int4 IDs? Is any of them clearly any better or worse than the others? Ralf Engelschall's OSSP uuid looks very good. Written in C with interfaces into PostgreSQL, PHP and C++ (classes wrapping the C structures and functions). http://www.ossp.org/pkg/lib/uuid/ You should be able to e. g. CREATE TABLE t (id UUID DEFAULT 'uuid(4)' PRIMARY KEY); -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] cannot open pg_database
# [EMAIL PROTECTED] / 2006-08-19 20:18:53 -0700: Installing with yum, Fedora core 5. Get error: could not open file global/pg_database: No such file or directory. The file exists however, in /var/lib/pgsql/data/global and contains 3 lines: postgres 10793 1663 499 499 template 1 1663 499 499 template0 10792 1663 499 499 From the user-comments in manual, chapter 17.1 : If you get an error like psql: FATAL: could not open file global/pg_database: No such file or directory make sure that in your init.d postgresql file (if you have one) or in the env variables for the shell that runs your server process that PGDATA is set properly. Then try to restart the server. If the server will not restart, check for an already running server process (sudo ps -af | grep postgres). Sometimes they can hang around, secretly, and screw things up. As newbie, reluctant to start editing the init.d file. Ideas appreciated. * bash-3.1$ su -c 'pg_ctl start -D /usr/local/pgsql/data -l serverlog' postgres Password: postmaster starting bash-3.1$ psql template1 psql: FATAL: could not open file global/pg_database: No such file or directory what does this output? su -c 'ls -l /usr/local/pgsql/data/global' postgres -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] select * from users where user_id NOT in (select
# [EMAIL PROTECTED] / 2006-08-18 10:00:20 +0200: On 8/18/06, Stephan Szabo [EMAIL PROTECTED] wrote: When the subselect returns NULL for at least one row, you fall into this sort of case. x NOT IN (...) is equivalent to NOT(x IN (...)) which is NOT(x = ANY (...)) x = ANY (...) is basically defined as True if x = y is true for some y in the subselect False if x = y is false for all y in the subselect Unknown otherwise Since x = NULL is unknown and not true or false, you fall into the last case with your query and data. I've fixed my problem now by: select user_id, username from phpbb_users where user_id not in (select ban_userid from phpbb_banlist where ban_userid is not null); but still your explanation feels illogical to me even though I know you're right... The confusion comes from mismatch between the meaning of NULL in languages like C where it means NONE, and SQL, where it's more like ANY/UNKNOWN. I believe it'll make sense once you buy the latter meaning. Since NULL means UNKNOWN, can you tell which ids from (1, 2, 3, 4) are ABSOLUTELY NOT in (1, UNKNOWN)? You can't, because you don't know what that UNKNOWN (IOW NULL) is. It is unknown whether an unknown value equals any other value: test=# select coalesce((1 = NULL)::int::text, 'UNKNOWN'); coalesce -- UNKNOWN (1 row) test=# select coalesce((NULL = NULL)::int::text, 'UNKNOWN'); coalesce -- UNKNOWN (1 row) Thus, given these data test=# create table a (id int); test=# create table b (id int); test=# insert into a values (1); test=# insert into a values (2); test=# insert into a values (3); test=# insert into a values (4); test=# insert into b values (1); test=# insert into b values (NULL); this query test=# select * from a where id not in (select * from b); must return an empty set, because the NULL in b might stand for any of the four values in a. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Triggers invoking a stored procedure or a C function
# [EMAIL PROTECTED] / 2006-08-16 11:55:39 -0400:
Hi,
Conventionally a trigger would fire a few sql queries on a particular event
and we have standard code for that.
My requirement is to start a stored procedure or a C function as a trigger
action.
Is this possible?
Besides the fact that PostgreSQL doesn't have stored procedures,
only SQL-invoked routines, both SQL and external, you can.
After all,
http://www.postgresql.org/docs/8.1/static/sql-createtrigger.html
says
CREATE TRIGGER name { BEFORE | AFTER } { event [ OR ... ] }
ON table [ FOR [ EACH ] { ROW | STATEMENT } ]
EXECUTE PROCEDURE funcname ( arguments )
The above page also links to 33.4. A Complete Example
(http://www.postgresql.org/docs/8.1/static/trigger-example.html),
which revolves around a trigger function written in C.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Tuning to speed select
# [EMAIL PROTECTED] / 2006-08-11 10:12:40 -0400: I think my Dell Precision 650 has SATA on the motherboard. The boss says I can order one drive, so what should I get? How much faster is RAID 0+1 than a single drive? You need 4 disks for 0+1 (or 1+0, also called 10). -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Restoring database from old DATA folder
# [EMAIL PROTECTED] / 2006-08-07 22:17:26 -0700: I have a DATA folder of my previous PostgreSQL 8.1 (Windows XP) installation. I do not have any DUMP or other backups. I want to use this folder instead of the newly created DATA folder after new installtion. I tried replacing the new folder with the old folder, but it did not work. pgAdmin also stopped working. Was the old postgres stopped when you created the copy? Was the new postgres stopped when you put the copy in? Did you restore correct permissions when you put the copy in? Is the new install the same version of postgres as the old one? What are the error messages? I want to know how to recover the database from a DATA folder in case of worst situations if no backups are available. http://www.postgresql.org/docs/8.1/static/backup-file.html http://www.postgresql.org/docs/8.1/static/migration.html -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL]
# [EMAIL PROTECTED] / 2006-08-05 19:32:27 +0200: I stop the postmaster service and then i copy the directory in an other place. Restart the postmaster and then test someting modifications. Then i stop the service and restore the saved data directory Now the postmaster dont start any more Wy? I'd guess you didn't preserve permissions on the data directory contents. What does PostgreSQL log? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem writing function
# [EMAIL PROTECTED] / 2006-08-06 11:47:43 +0200:
the following function is created properly:
CREATE OR REPLACE FUNCTION insert_into_table_overview(text, chr integer)
RETURNS void AS '
DECLARE
in_tableALIAS FOR $1;
p RECORD;
BEGIN
RAISE NOTICE ''in_table = %'', in_table;
FOR p IN EXECUTE ''select distinct pid from '' ||
quote_ident(in_table) LOOP
EXECUTE ''insert into table_overview(table_name, chr,
start_no, end_no, pid)
select '' || quote_ident(tname) || '', chr,
should the tname be in_table?
min(entry_no), max(entry_no), p from '' || quote_ident(in_table);
END LOOP;
END;
' LANGUAGE plpgsql;
But when i execute it with select
insert_into_table_overview('test1'::text, 1); i only get the following
output:
NOTICE: in_table = test1
ERROR: relation test1 does not exist
CONTEXT: SQL statement select distinct pid from test1
PL/pgSQL function insert_into_table_overview line 6 at for over
execute statement
I am sure that there is something wrong with the quotes, but i just
can't find out what.
Is there a table called test1?
SELECT * FROM test1;
SELECT * FROM test1;
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] proper use of array datatype
# [EMAIL PROTECTED] / 2006-08-02 10:49:01 -0700: On 8/1/06, Reece Hart [EMAIL PROTECTED] wrote: Eric Andrews wrote: I am not much of a schema designer and have a general questoin about the proper use of the array datatype. In my example, I have destinations, and destinations can have multiple boxes, and inside those boxes are a set of contents. what I want to do is search and basically mine data from the content sets. I would use arrays exclusively for data sets for which each datum is meaningless by itself (for example, a single coordinate in 3D, although there are better ways to handle points in postgresql). I would recommend against using arrays for any data you wish to mine, and instead recast these has-a relationships as many-to-one joins across at least two tables. For example, a row from the table destination has-a (joins to) rows from boxes, and a box has-a (joins to) contents. how would these tables look though? I cant have a table for each set of contents in a box... You need to rotate your brains 90 degrees. You cant have a distinct set of columns (a table) for each set, but you can have have a distinct set of rows (a set) for each, ummm, set. The language suggests it's a better model, and indeed it is: CREATE TABLE destination ( destid SERIAL PRIMARY KEY, destname VARCHAR -- ... ); CREATE TABLE box ( boxid SERIAL PRIMARY KEY, destid INT REFERENCES destination (destid) -- ... ); CREATE TABLE box_contents ( boxid SERIAL REFERENCES box (boxid), thing TEXT -- ... ); SELECT * FROM box_contents JOIN box USING (boxid) JOIN destination USING (destid) WHERE destination.destname = 'foo'; -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] LISTEN considered dangerous
# [EMAIL PROTECTED] / 2006-08-02 07:57:55 +0200: I'm bothered by listen listening from the end of the transaction in stead of the start of the transaction. Sorry if this isn't what you're after, instead just a question: Why don't you issue the LISTEN in a separate transaction before going on? LISTEN foo; BEGIN; SELECT ... -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Triggers in Postgres
# [EMAIL PROTECTED] / 2006-07-31 11:58:49 -0400: Actually Postgres manual of triggers says that in postgres, you can't write a trigger in conventional sql. You have to write it in a procedural language like C. So wanted some more insight on it. ~Jas Where does it say so? Do you have a link? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Triggers in Postgres
# [EMAIL PROTECTED] / 2006-08-01 02:35:48 -0400: On 8/1/06, Roman Neuhauser [EMAIL PROTECTED] wrote: # [EMAIL PROTECTED] / 2006-07-31 11:58:49 -0400: Actually Postgres manual of triggers says that in postgres, you can't write a trigger in conventional sql. You have to write it in a procedural language like C. So wanted some more insight on it. ~Jas Where does it say so? Do you have a link? http://www.postgresql.org/docs/8.1/interactive/triggers.html it says something like this: It is not currently possible to write a trigger function in the plain SQL function language. though lately I saw triggers written in pure sql in postgres Notice that the manual doesn't mention C, and I guess those pure sql triggers were written in PL/PgSQL, a procedural language. As the following example fails to demonstrate, it's just SQL with a few control structures, very easy to get running if you have a bit of SQL and programming background. CREATE TABLE t (x SERIAL); CREATE FUNCTION sqlf() RETURNS SETOF t STABLE LANGUAGE SQL AS $$ SELECT * FROM t; $$; CREATE FUNCTION plpgsqlf() RETURNS SETOF t STABLE LANGUAGE PLPGSQL AS $$ DECLARE r t; BEGIN FOR r IN SELECT * FROM t LOOP RETURN NEXT r; END LOOP; END; $$; -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] money type depreciated?
# [EMAIL PROTECTED] / 2006-08-01 10:18:45 -0700: I read in the documentation that the money type is depreciated. It says to use the to_char function and NUMERIC/decimal instead. Why was the money type depreciated when it was so useful? How would be the best way to use to_char and numeric to replace that type since I don't want to be using a depreciated data type. Use a custom type; IIRC Martijn van Oosterhout has something. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Messages to pgsql-general list not being posted
# [EMAIL PROTECTED] / 2006-07-19 09:50:05 -0400: Yesterday I sent two messages to pgsql-general@postgresql.org, and neither one posted, as far as I can tell. Please explain the gin index - 7/18/06 10:44 AM EDT number of distinct values in tsearch2 gist index - 7/18/06 1:24 PM EDT Both are in my pgsql-general maildir, message ids: [EMAIL PROTECTED] [EMAIL PROTECTED] -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] pgsql vs mysql
# [EMAIL PROTECTED] / 2006-07-11 12:04:07 -0400: On 6/30/2006 1:07 PM, Merlin Moncure wrote: * mysql has a few features here and there which are nice...just to name a few, flush tables with lock, multiple insert, etc (...) The multiple insert stuff is not only non-standard, it also encourages the bad practice of using literal values directly in the SQL string versus prepared statements with place holders. It is bad practice because it introduces SQL injection risks since the responsibility of literal value escaping is with the application instead of the driver. Everything that teaches new developers bad things counts as a disadvantage in my book, so -1 on that for MySQL too. Those multiple inserts are really inserts with Table Value Constructors, which are table literals, SQL:2003 F641 (see http://www.postgresql.org/docs/8.1/static/unsupported-features-sql-standard.html). TVCs are useful in many more contexts than just inserts. An email from a past thread on this list: : # [EMAIL PROTECTED] / 2005-09-20 20:45:21 +0200: : I was thinking if this was possible in some way.. : I have this table where we have X and Y coordinates, and i need to : select several in one go. : : # select * from xy where (x = 1 and y = 2) or (x = 2 and y = 2); : : This works but are not so nice looking. : It would be nice to be able to do it like this: : : # select * from xy where (x, y) in ((1, 2), (2, 2)); : : But that doesn't work. : A funny thing is that this works: : : # select * from xy where (x, y) = (1, 2); : : What's the most efficient way of doing these kind of selects? : : You'd need to write that as : : (x, y) IN VALUES (1, 2), (2, 2) : : Unfortunately, PostgreSQL's support for table value constructors : is very weak. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Version/Change Management of functions?
# [EMAIL PROTECTED] / 2006-07-07 14:08:08 -0600: --On July 7, 2006 12:35:53 PM + Roman Neuhauser [EMAIL PROTECTED] wrote: # [EMAIL PROTECTED] / 2006-07-06 22:41:27 -0600: OK I know this is an odd question but I'm working on an app that will rely more and more on database driven functions, and while the app's source is in SVN, and I intend for the source of the SQL scripts to also be there, I was wondering...what are people doing for version control and change management on their custom (esp PL/pgSQL and say PL/Perl) functions? Well, people use a version control system. Do you have any specific questions? Yes, how exactly do you use it. Since there's no way to directly control whats in the DB via a VCS, further, how do you verify that what is in the DB is also in the VCS, etc? Simply: there's no the DB. Developers have no access to the customer installation(s), and putting things into the VCS is the only way for them to produce code, which includes DDL. What's not in the VCS (or generaged during the release process from tools that are tracked) cannot be installed from the tarball. Whether you produce releases for general consumption like the PostgreSQL project does or your installed base consists of a single web server, whether you produce tarballs or run svn up on the single Apache box: that's an irrelevant detail of the release process. Each release should contain DDL/DML needed to upgrade the database from the previous release. The developer who wants to change the schema must provide the ALTER statements. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Why my cursor construction is so slow?
# kleptog@svana.org / 2006-06-22 09:19:44 +0200: On Tue, Jun 20, 2006 at 02:06:19AM -0700, [EMAIL PROTECTED] wrote: Such construction is very slow but when I modify SQL to: OPEN cursor1 FOR SELECT * FROM alias WHERE mask=alias_out ORDER BY mask LIMIT 100; it works very fast. It is strange for me becuase I've understood so far that when cursor is open select is executed but Postgres does not select all rows - only cursor is positioned on first row, when you execute fetch next row is read. But this example shows something different. PostgreSQL tries to optimise for overall query time. Without the limit it tries to find a plan that will return the whole set as quick as possible. That looks like the wrong approach for a cursor. With the LIMIT it might take a different approach, which might be worse if you read the whole lot, but better for a limited set. A fast-start plan so to speak. That looks like a better approach for a cursor. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Version/Change Management of functions?
# [EMAIL PROTECTED] / 2006-07-06 22:41:27 -0600: OK I know this is an odd question but I'm working on an app that will rely more and more on database driven functions, and while the app's source is in SVN, and I intend for the source of the SQL scripts to also be there, I was wondering...what are people doing for version control and change management on their custom (esp PL/pgSQL and say PL/Perl) functions? Well, people use a version control system. Do you have any specific questions? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing
# [EMAIL PROTECTED] / 2006-06-09 10:12:21 +0200: Agent M wrote: If you don't use NULL, then you don't come across 3-valued logic--problem solved. So was does SELECT sum(1) FROM dual WHERE false return? You stripped this: Some Tutorial D notions really make sense; I would love to be able to rely on every function returning a relation. So your query would prolly return a table with one (INT) column, but no rows. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to use index in case insensitive substing search
# [EMAIL PROTECTED] / 2006-06-06 11:58:26 +0300: How to force postgres to use index for the following query (I can change the query to equivalent if required) select nimi from klient where lower(nimi) like 'test%' do you have an index on klient (lower(nimi))? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] What is the point of create or replace view command
# [EMAIL PROTECTED] / 2006-06-05 16:19:19 +1000: On 6/5/06, Joe Conway wrote: Chris Velevitch wrote: But what about my original question? What is the point of the create or replace view command if you can't change the column and data types? -- create table t1 create table t1(f int); -- create view v1 based on table t1 create view v1 as select * from t1; -- create view v2 based on view v1 create view v2 as select * from v1; -- attempt to drop and recreate view v1 with a WHERE clause drop view v1; -- attempt to create or replace view v1 with a WHERE clause create or replace view v1 as select * from t1 where 1 = 1; -- attempt to create or replace view v1 with a different WHERE clause create or replace view v1 as select * from t1 where 1 = 2; Sorry, I don't understand what you are trying to say as these examples don't change the number of columns, type of column or column names. So according to the description of the command, these will work. The point of the create or replace view command is that you may need to change other attributes of the view (see Joe's examples), and then the [OR REPLACE] clause is useful. Now, I don't disagree that being able to CREATE OR REPLACE even when the interface changes wouldn't be useful. You're welcome to provide a patch that does this at least when there are no objects that depend on the view in question. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Querying for strings that match after prefix
# [EMAIL PROTECTED] / 2006-06-02 05:18:08 -0700: I think I need to explain a bit further. I tried simply using update people replace(address, 'mailto:',''); but unfortunately that produced a duplicate key error as some of the addresses prefixed with 'mailto:' are already present (unprefixed) in the table. So what I need to do is find those entries - those items in the table for which there is an equivalent entry prefixed with 'mailto:'. Sorry if I'm not being very clear! Not unclear, this question is a completely different animal. Pick one: SELECT p.* FROM people p, (SELECT REPLACE(address, 'mailto:', '') AS stripped FROM people WHERE address LIKE 'mailto:%') AS m WHERE p.address = m.stripped; SELECT * FROM people p WHERE p.address IN ( SELECT REPLACE(address, 'mailto:', '') AS stripped FROM people WHERE address LIKE 'mailto:%'); -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] DB structure for logically similar objects in different
# [EMAIL PROTECTED] / 2006-05-29 08:10:43 -0400: Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2006-05-28 16:13:20 -0400: Basically we've got several different states that an item can be in. From what I've seen the way many places seem to deal with them is something along the lines of making bool values that act as switches... Ex: table items: item_id name description is_active is_sold_out is_banned Now we've started to see some problems with this sort of design. Namely we need to run sanity tests on every page that hits the items table to make sure is_active is true, is_sold_out is false, is_banned is false so on and so forth. I was thinking of splitting up states into different tables ala... table items_active: item_active_id name description table items_sold_out: item_sold_out_id name description would views help? CREATE VIEW items_to_sell AS SELECT item_id, name, description FROM items WHERE is_active = 1 AND is_sold_out = 0 AND is_banned = 0; Views work for querying the chunks of data that match different states, but if I was looking for information based on a single item_id wouldn't I still need the sanity checks? No. SELECT * FROM items_to_sell WHERE item_id = 123 will be transformed into something like SELECT item_id, name, description FROM items WHERE item_id = 123 AND is_active = 1 AND is_sold_out = 0 AND is_banned = 0 -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] DB structure for logically similar objects in different states...
# [EMAIL PROTECTED] / 2006-05-28 16:13:20 -0400: Basically we've got several different states that an item can be in. From what I've seen the way many places seem to deal with them is something along the lines of making bool values that act as switches... Ex: table items: item_id name description is_active is_sold_out is_banned Now we've started to see some problems with this sort of design. Namely we need to run sanity tests on every page that hits the items table to make sure is_active is true, is_sold_out is false, is_banned is false so on and so forth. I was thinking of splitting up states into different tables ala... table items_active: item_active_id name description table items_sold_out: item_sold_out_id name description would views help? CREATE VIEW items_to_sell AS SELECT item_id, name, description FROM items WHERE is_active = 1 AND is_sold_out = 0 AND is_banned = 0; -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgAdmin3 question
# [EMAIL PROTECTED] / 2006-04-07 14:31:04 -0700: A further question: we are using Debian system. So when we leave the 'Address' box blank (on the Add Server page of pgAdmin), according to the help file, it will go to use the default Postgresql socket on the local machine. We actually have a line as 'local all all ident sameuser' in the pg_hba.conf file. But when I tried, pgAdmin couldn't log the user in and shows Ident Authentication Failed. Then I modified the line pg_hba.conf file to 'local all all ident', and aaded a map line in the pg_ident.conf file to map the new user to user 'postgres'. Still not work. Did you instruct postmaster to reread the config files? http://www.postgresql.org/docs/8.1/static/client-authentication.html#AUTH-PG-HBA-CONF The pg_hba.conf file is read on start-up and when the main server process (postmaster) receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload or kill -HUP) to make it re-read the file. http://www.postgresql.org/docs/8.1/static/auth-methods.html#AUTH-IDENT The pg_ident.conf file is read on start-up and when the main server process (postmaster) receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload or kill -HUP) to make it re-read the file. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres Library natively available for Mac OSX Intel?
# [EMAIL PROTECTED] / 2006-04-08 14:04:28 +0200: Am 07.04.2006 um 13:50 schrieb User Roman: # [EMAIL PROTECTED] / 2006-03-31 10:05:06 +0200: I would like to know if somebody already has a Mac OSX Intel 10.4.5 pg-Library (for C, C++, Objective C) or knows how to compile it? What problems did you have building libpq? Note: I'm not an OSX user. I just wanted to know - I would like to have universal binaries of libpg and psql to deploy. Currently 8.1.3 compiles and runs just fine on OSX 10.4.6 + XCode 2.2.1, but generates binaries just for the current host architecture. Now when I add -arch i386 -arch ppc to CFLAGS and LDFLAGS for configure, then it compiles everything just fine, however at linking stage I get various problems for missing architecture files. Every generated .o file in the src build tree is actually an universal binary now, like for example sorry, this is way over my head. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] FAQ 1.1
# [EMAIL PROTECTED] / 2006-04-06 12:03:18 -0400: On Apr 3, 2006, at 11:23 PM, Chris Browne wrote: Yeah, someone at the office was asking me on the elevator about whether some Post-something was somehow up and coming. In retrospect, I think he was trying to pronounce Postgre, and arrived at something I had never heard before... And it doesn't help when post-something could also mean a very popular MTA... Looks like a missed opportunity for a April 1st announcement of disambiguating the two by renaming postgres to pregross. :) -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Postgres Library natively available for Mac OSX Intel?
# [EMAIL PROTECTED] / 2006-03-31 10:05:06 +0200: I would like to know if somebody already has a Mac OSX Intel 10.4.5 pg-Library (for C, C++, Objective C) or knows how to compile it? What problems did you have building libpq? Note: I'm not an OSX user. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Use functions in Pl/Perl language
# [EMAIL PROTECTED] / 2006-03-29 17:17:48 -0800: Hello, I would like to create a function that lets me to search in a table rows according differents parameters. I looked in the documentation of postgresql 8.1 about Pl/Perl procedures but I didn't found how to cross each row of a table with Pl/Perl. Someone would have an example of that with Pl/Perl language ? see the lotsa_md5() example at http://www.postgresql.org/docs/8.1/static/plperl-database.html -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] ADO.NET ExecuteReader returns no rows
Hello,
Someitmes ExectueReader doesn't return rows, if I repeat the method it
does!!
I've got code like that:
using (NpgsqlDataReader readFrames =
command.ExecuteReader())
{
if (!readFrames.HasRows)
{
System.Diagnostics.Debugger.Break();
}
while (readFrames.Read())
{
currentMaxDate =
(DateTime)readFrames[idvideodate];
mediaData.Add((byte[])readFrames[mediabinary]);
}
readFrames.Close();
}
That part of code executes multiple times and always, soon or later
I've got reader without rows.. When I execute that statement again:
the same parameters - ExecuteReader() returns rows (as it should).
The problem occurs in both versions: 0.71 and in 1.0 beta 2.
I've also tried commercial driver from Core Lab.. with the same
result.
PostgresSQL version 8.1.2.
The field idvideodate is Timestamp and mediabinary is bytea.
Probably it's a problem not with ado.net driver, but with
PostgressSQL but how to cope with that?
Roman.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] ADO.NET ExecuteReader returns no rows
I've compared Debug log between correct and wrong activity: Good activity: 2006-03-24 16:30:36 2632Debug String written: select * from getvideodata(2::int4, 1::int4, '2006-03-24 16:30:31.40'::timestamp, 1::int4) AS (idvideodate timestamp, mediabinary bytea). 2006-03-24 16:30:36 2632Debug Entering NpgsqlState.ProcessBackendResponses() 2006-03-24 16:30:36 2632Debug RowDescription message received from server. 2006-03-24 16:30:36 2632Debug Entering NpgsqlRowDescription.ReadFromStream_Ver_3() 2006-03-24 16:30:36 2632Debug Entering PGUtil.ReadString() 2006-03-24 16:30:36 2632Debug String read: idvideodate. 2006-03-24 16:30:36 2632Debug Entering PGUtil.ReadString() 2006-03-24 16:30:36 2632Debug String read: mediabinary. 2006-03-24 16:30:36 2632Debug DataRow message received from server. 2006-03-24 16:30:36 2632Debug Entering NpgsqlAsciiRow.NpgsqlAsciiRow() 2006-03-24 16:30:36 2632Debug Entering NpgsqlAsciiRow.ReadFromStream_Ver_3() 2006-03-24 16:30:36 2632Debug Entering NpgsqlTypesHelper.ConvertBackendStringToSystemType() 2006-03-24 16:30:37 2632Debug Entering NpgsqlTypesHelper.ConvertBackendStringToSystemType() 2006-03-24 16:30:38 2632Debug Entering PGUtil.ReadString() 2006-03-24 16:30:38 2632Debug String read: SELECT. 2006-03-24 16:30:38 2632Debug CompletedResponse message from Server: SELECT. 2006-03-24 16:30:38 2632Debug ReadyForQuery message received from server. 2006-03-24 16:30:38 2632Debug Entering PGUtil.ReadString() 2006-03-24 16:30:38 2632Debug String read: I. ... Wrong activity: 2006-03-24 16:30:38 2632Debug String written: select * from getvideodata(2::int4, 1::int4, '2006-03-24 16:30:35.40'::timestamp, 1::int4) AS (idvideodate timestamp, mediabinary bytea). 2006-03-24 16:30:38 2632Debug Entering NpgsqlState.ProcessBackendResponses() 2006-03-24 16:30:38 2632Debug RowDescription message received from server. 2006-03-24 16:30:38 2632Debug Entering NpgsqlRowDescription.ReadFromStream_Ver_3() 2006-03-24 16:30:38 2632Debug Entering PGUtil.ReadString() 2006-03-24 16:30:38 2632Debug String read: idvideodate. 2006-03-24 16:30:38 2632Debug Entering PGUtil.ReadString() 2006-03-24 16:30:38 2632Debug String read: mediabinary. 2006-03-24 16:30:38 2632Debug Entering PGUtil.ReadString() 2006-03-24 16:30:38 2632Debug String read: SELECT. 2006-03-24 16:30:38 2632Debug CompletedResponse message from Server: SELECT. 2006-03-24 16:30:38 2632Debug ReadyForQuery message received from server. 2006-03-24 16:30:38 2632Debug Entering PGUtil.ReadString() 2006-03-24 16:30:38 2632Debug String read: I. 2006-03-24 16:30:38 2632Debug Entering NpgsqlState.ChangeState() 2006-03-24 16:30:38 2632Debug Entering NpgsqlCommand.UpdateOutputParameters() 2006-03-24 16:30:38 2632Debug Get NpgsqlCommand.Connection 2006-03-24 16:30:48 2632Debug Entering NpgsqlDataReader.Read() 2006-03-24 16:30:48 2632Debug Entering NpgsqlDataReader.Dispose() 2006-03-24 16:30:48 2632Debug Entering NpgsqlConnection.Close() ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
Let me start this email by saying thank you to whoever fixed the problem. I found a bunch of Welcome to... / Results from delayed command message pairs in my mail this morning, and a batch of messages from each of sql, performance, and hackers mailing lists. # [EMAIL PROTECTED] / 2006-02-28 20:07:09 -0400: %mj_shell -p who pgsql-general | grep -i sigpipe.cz [EMAIL PROTECTED] [EMAIL PROTECTED] In fact, he's been registered since Jul '05: Yes, that was the date I first received must be approved by maintainers messages. However, I didn't receive any email to that address until Monday when I retried the same subscribe-set pgsql-general noprefix [EMAIL PROTECTED] command, only this time I sent it from the [EMAIL PROTECTED] address, not [EMAIL PROTECTED] A few minutes later, I had majordomo's welcome to and the traffic started flowing in. My other subscriptions (sql, hackers, performance) were in limbo until this morning (CET). Address: [EMAIL PROTECTED] Address is valid. Address Mailbox: [EMAIL PROTECTED] Registered as [EMAIL PROTECTED] Registered onWed Jul 27 06:44:37 2005 Data last changed on Mon Feb 27 09:20:21 2006 Subscribed to1 lists pgsql-general: Subscribed as [EMAIL PROTECTED] Subscribed on Mon Feb 27 09:20:21 2006 Last changed on Mon Feb 27 09:20:21 2006 Receiving each message as it is posted Subscriber flags: prefix That's strange, as you can see above, the command I sent was subscribe-set $list noprefix, yet the listing shows prefix. I can confirm that messages I receive from all the lists have munged subjects, although I used subscribe-set noprefix in all subscriptions. So I'm not 100% certain *what* the problem is :( majordomo claimed I was subscribed, but didn't send me the traffic. # [EMAIL PROTECTED] / 2006-03-01 04:07:13 -: Marc, I was able to view that weird whitespace message by going to Subcribers, entering neuhauser in the search box, and then clicking[1] on the troublesome name in question. Greg Sabino Mullane [EMAIL PROTECTED] That's a completely different bug, the web interface doesn't escape the +, which is then interpreted as urlencoded space. You might want to make sure that the web interface protects the addresses it uses as request parameters by encoding / decoding them according to RFC1738. Otherwise, malicious users might be able to use specially crafted email addresses as trojan horses. Again, thanks for approving the subscriptions. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
# [EMAIL PROTECTED] / 2006-02-27 16:32:55 -0400: On Mon, 27 Feb 2006, Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2006-02-26 19:01:58 -0400: 'k, I just checked all the lists you listed, and you are subscribed to each of them ... are you not receiving messages? (...) Can you try something more recent then last year? Here's a copy of an email I sent you off list documenting stalled-for-approval requests from yesterday. : Date: Mon, 27 Feb 2006 15:43:54 +0100 : From: Roman Neuhauser [EMAIL PROTECTED] : To: Marc G. Fournier [EMAIL PROTECTED] : Subject: Re: [GENERAL] majordomo unmaintained, postmaster emails ignored? : Message-ID: [EMAIL PROTECTED] : References: [EMAIL PROTECTED] [EMAIL PROTECTED] : Mime-Version: 1.0 : Content-Type: text/plain; charset=us-ascii : Content-Disposition: inline : In-Reply-To: [EMAIL PROTECTED] : User-Agent: Mutt/1.5.9i : Status: RO : : Note: I've removed pgsql-general@ from the recipient list so as to : reduce clutter. Feel free to add it again for you reply if you feel : this is relevant on that list. : : # [EMAIL PROTECTED] / 2006-02-26 14:36:47 -0400: : On Sun, 26 Feb 2006, Roman Neuhauser wrote: : I've been waiting five months for the majordomo moderators to approve : my subscription requests to several @postgresql.org mailing lists. : : the majordomo moderators don't have to approve subscribe requests : : The above is false under *certain circumstances*. Marc, can you : tell me what attribute(s) of these requests cause(s) majordomo to : wait for approval? : : I've just had this exchange with [EMAIL PROTECTED] (only the hackers : request is put here for brevity): : : : Date: Mon, 27 Feb 2006 15:28:18 +0100 : : From: Roman Neuhauser [EMAIL PROTECTED] : : To: [EMAIL PROTECTED] : : : : subscribe-set pgsql-docs noprefix [EMAIL PROTECTED] : : subscribe-set pgsql-hackers noprefix [EMAIL PROTECTED] : : subscribe-set pgsql-performance noprefix [EMAIL PROTECTED] : : subscribe-set pgsql-sql noprefix [EMAIL PROTECTED] : : : Date: Mon, 27 Feb 2006 10:28:18 -0400 : : From: [EMAIL PROTECTED] : : To: Roman Neuhauser [EMAIL PROTECTED] : : Subject: Majordomo results : : : : : : subscribe-set pgsql-hackers noprefix [EMAIL PROTECTED] : : The subscribe command did not succeed. : : : : The request : : subscribe-set pgsql-hackers noprefix [EMAIL PROTECTED] : : must be confirmed by : : [EMAIL PROTECTED] : : and approved by the moderators. Confirmation instructions have been : : mailed in a separate message. : : : : : Date: Mon, 27 Feb 2006 10:28:17 -0400 : : From: [EMAIL PROTECTED] : : To: [EMAIL PROTECTED] : : Subject: BC9A-BD57-4C44 : CONFIRM from pgsql-hackers (subscribe) : : : : __ : : The following request : : : : subscribe-set pgsql-hackers noprefix [EMAIL PROTECTED] : : : : was sent to : : by Roman Neuhauser [EMAIL PROTECTED]. : : : : To accept or reject this request, please do one of the following: : (...) : : : Date: Mon, 27 Feb 2006 15:29:11 +0100 : : From: Roman Neuhauser [EMAIL PROTECTED] : : To: [EMAIL PROTECTED] : : Subject: Re: BC9A-BD57-4C44 : CONFIRM from pgsql-hackers (subscribe) : : : : accept BC9A-BD57-4C44 : : : Date: Mon, 27 Feb 2006 10:29:13 -0400 : : From: [EMAIL PROTECTED] : : To: Roman Neuhauser [EMAIL PROTECTED] : : Subject: Majordomo results: Re: BC9A-BD57-4C44 : CONFIRM from pgsql- : : : : : : accept BC9A-BD57-4C44 : : The accept command for token BC9A-BD57-4C44 succeeded, : : but further approval is needed. : : : : Now the request must be approved by the moderators. : : The results will be mailed to you after this is done. : : : : : : : : Valid commands processed: 1 : : 0 succeeded, 1 stalled, and 0 failed. : : : : : : Use the following command: : : sessioninfo 530ce04b8a9f02fee27a58acb99d9cb88a092ae2 : : to see technical information about this session. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
# [EMAIL PROTECTED] / 2006-02-28 15:14:39 -:
It looks like the listserv cannot handle that address - it says:
Individual words are not allowed in an e-mail address without an
intervening period or at symbol ('.' or '@').
Which address? I see no whitespace in any of the addresses displayed
in my email.
Perhaps you could simply use your normal email address, and filter
on the List-ID header? (Assuming you are setting up that expanded
email address to help with filtering). Feel free to email me offlist
as well.
I'm switching away from header-based filtering because that breaks
from time to time because of version and/or configuration changes in
the software involved.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
# [EMAIL PROTECTED] / 2006-02-26 19:01:58 -0400: 'k, I just checked all the lists you listed, and you are subscribed to each of them ... are you not receiving messages? I'm not receiving messages because I'm subscribed with nomail. That's not the problem however. I want to receive the list traffic to a different set of addresses, and that stalled (the request must be approved by the moderators), as well as my attempts to gain pgsql-docs-owner@'s and postmaster@'s attention. Does anyone read mail for the pgsql-list-owner@ and postmaster@ aliases? Does anyone pay attention to the approval queue? Anyway, here's a few snippets from my last year's email conversations with [EMAIL PROTECTED] My subscription requests: : Date: Sat, 24 Sep 2005 12:17:37 +0200 : From: Roman Neuhauser [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : : subscribe-set pgsql-docs noprefix [EMAIL PROTECTED] : subscribe-set pgsql-general noprefix [EMAIL PROTECTED] : subscribe-set pgsql-hackers noprefix [EMAIL PROTECTED] : subscribe-set pgsql-performance noprefix [EMAIL PROTECTED] : subscribe-set pgsql-sql noprefix [EMAIL PROTECTED] : subscribe-set pgsql-docs nomail [EMAIL PROTECTED] : subscribe-set pgsql-general nomail [EMAIL PROTECTED] : subscribe-set pgsql-hackers nomail [EMAIL PROTECTED] : subscribe-set pgsql-performance nomail [EMAIL PROTECTED] : subscribe-set pgsql-sql nomail [EMAIL PROTECTED] Majordomo replied with the usual batch of CONFIRM messages, one of which was: : Date: Sat, 24 Sep 2005 07:17:39 -0300 : From: [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Subject: CA86-08AC-51A7 : CONFIRM from pgsql-general (subscribe) : : __ : The following request : : subscribe-set pgsql-general noprefix [EMAIL PROTECTED] : : was sent to : by Roman Neuhauser [EMAIL PROTECTED]. : : To accept or reject this request, please do one of the following: : : 1. If you have web browsing capability, visit : http://mail.postgresql.org/mj/mj_confirm/domain=postgresql.org?t=CA86-08AC-51A7 :and follow the instructions there. : : 2. Reply to [EMAIL PROTECTED] :with one of the following two commands in the body of the message: : : accept : reject : :(The number CA86-08AC-51A7 must be in the Subject header) : : 3. Reply to [EMAIL PROTECTED] :with one of the following two commands in the body of the message: : : accept CA86-08AC-51A7 : reject CA86-08AC-51A7 : : Your confirmation is required for the following reason(s): : : Roman Neuhauser [EMAIL PROTECTED] issued a command : that affects another address ([EMAIL PROTECTED]). : : : If you do not respond within 4 days, a reminder will be sent. : : If you do not respond within 7 days, this token will expire, : and the request will not be completed. : : If you would like to communicate with a person, : send mail to [EMAIL PROTECTED] I replied to that with: : Date: Sat, 24 Sep 2005 12:37:28 +0200 : From: Roman Neuhauser [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Subject: Re: CA86-08AC-51A7 : CONFIRM from pgsql-general (subscribe) : : accept To which majordomo said: : Date: Sat, 24 Sep 2005 07:37:29 -0300 : From: [EMAIL PROTECTED] : To: Roman Neuhauser [EMAIL PROTECTED] : Subject: Majordomo results: Re: CA86-08AC-51A7 : CONFIRM from pgsql- : : : accept : The accept command for token CA86-08AC-51A7 succeeded, : but further approval is needed. : : Now the request must be approved by the moderators. : The results will be mailed to you after this is done. : : : : Valid commands processed: 1 : 0 succeeded, 1 stalled, and 0 failed. : : : Use the following command: : sessioninfo 12bbdbfc049f53e9c1782cc9c10c6310e23e504f : to see technical information about this session. And that was the end of the conversation, I'm still waiting for a reply to my message to postmaster@ from December, and another one to pgsql-docs-owner@ from last August (covering the same problem): : Date: Wed, 3 Aug 2005 00:40:15 +0200 : From: Roman Neuhauser [EMAIL PROTECTED] : To: [EMAIL PROTECTED] : Subject: majordomo command processing problems? : : Hello, : : I tried to subscribe to pgsql-docs seven days ago: : : subscribe pgsql-docs [EMAIL PROTECTED] : The subscribe command did not succeed. : : The request : subscribe pgsql-docs [EMAIL PROTECTED] : must be confirmed by : [EMAIL PROTECTED] : and approved by the moderators. Confirmation instructions have been : mailed in a separate message
[GENERAL] majordomo unmaintained, postmaster emails ignored?
Hello, I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. I sent an email to [EMAIL PROTECTED] more than two months ago, also without any reaction. What should I do to spark someone's interest? Pls cc me on replies. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
# [EMAIL PROTECTED] / 2006-02-26 14:36:47 -0400: On Sun, 26 Feb 2006, Roman Neuhauser wrote: Hello, I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. the majordomo moderators don't have to approve subscribe requests, you will auto-receive an email message from the list confirming that you do want to subscribe though ... its a fairly automated procedure ... if you aren't receiving the 'confirm' messages, then there is an overall problem with mail deliver ... checking pgsql-general, you are already subscribed ... what other lists are you trying to get onto? A copy of the message I sent to [EMAIL PROTECTED] last december is attached. I think it has all the evidence of approval being required you need. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---BeginMessage--- Hello, I tried to subscribe to the doc mailing list on July 27th, with token 9E0C-7AF7-36CA. The request has been processed up to : accept 9E0C-7AF7-36CA : The accept command for token 9E0C-7AF7-36CA succeeded, : but further approval is needed. : : Now the request must be approved by the moderators. : The results will be mailed to you after this is done. : : : : Valid commands processed: 1 : 0 succeeded, 1 stalled, and 0 failed. : : Use the following command: : sessioninfo d9a46448c54d3b48ad049e79e4c46dbaa9ee79f7 : to see technical information about this session. Nothing happened since then. I also tried to subscribe to general, hackers, perf, and sql on September 24th, with tokens CA86-08AC-51A7, 225E-80E3-7C1A, B43A-4209-5756, and 74A6-0E96-E08F, with exactly the same result. Could someone point please process the requests? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---End Message--- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
# [EMAIL PROTECTED] / 2006-02-26 20:15:20 +0100: # [EMAIL PROTECTED] / 2006-02-26 14:36:47 -0400: On Sun, 26 Feb 2006, Roman Neuhauser wrote: Hello, I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. the majordomo moderators don't have to approve subscribe requests, you will auto-receive an email message from the list confirming that you do want to subscribe though ... its a fairly automated procedure ... if you aren't receiving the 'confirm' messages, then there is an overall problem with mail deliver ... checking pgsql-general, you are already subscribed ... what other lists are you trying to get onto? A copy of the message I sent to [EMAIL PROTECTED] last december is attached. I think it has all the evidence of approval being required you need. Please do let me know if I need to submit the subscription requests identified in the email again. I'd really appreciate if someone did help me through this. BTW, my first never replied to email to people who should take care of majordomo I could find is a mail sent to [EMAIL PROTECTED] on Wed, 3 Aug 2005 00:40:15 +0200 (Message-ID: [EMAIL PROTECTED]). -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] non-btree primary key
Hello, looks like PostgreSQL (8.0/8.1) has no support for using other-than-btree indexes for primary keys. Is there a (perhaps un(der)documented) way to specify the index type? Rationale: I'm trying to have PKs on a type that defines only the = and operators, and would work with a hash-based PK. I know I could work around it with a UNIQUE INDEX, but would like to avoid that for exactly the reason described in the manual: : Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT : NULL, but identifying a set of columns as primary key also provides : metadata about the design of the schema, as a primary key implies : that other tables may rely on this set of columns as a unique : identifier for rows. Please cc me on replies, I have problems with the postgresql.org majordomo. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] More efficient INs when comparing two columns
# [EMAIL PROTECTED] / 2005-09-20 20:45:21 +0200: I was thinking if this was possible in some way.. I have this table where we have X and Y coordinates, and i need to select several in one go. # select * from xy where (x = 1 and y = 2) or (x = 2 and y = 2); This works but are not so nice looking. It would be nice to be able to do it like this: # select * from xy where (x, y) in ((1, 2), (2, 2)); But that doesn't work. A funny thing is that this works: # select * from xy where (x, y) = (1, 2); What's the most efficient way of doing these kind of selects? You'd need to write that as (x, y) IN VALUES (1, 2), (2, 2) Unfortunately, PostgreSQL's support for table value constructors is very weak. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Unsubcribe
# [EMAIL PROTECTED] / 2005-09-13 18:28:07 -0700: Please...help me.. How to unsubcribe Look at the headers of any message posted to the list. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] arrays, composite types
I'm looking for an equivalent of my_composite_type[] for use as a parameter of a pl/pgsql function. What do people use to dodge this limitation? Background: I have a few plpgsql functions that basically accept an array of objects decomposed into arrays of the objects' attributes: CREATE FUNCTION do_foo(int4, int4[], int4[], varchar[]) RETURNS int4 VOLATILE which I'd like to convert to CREATE FUNCTION do_foo(int4, myctype[]) RETURNS int4 VOLATILE so that the functions only need recompilation when myctype changes. myctype is CREATE TYPE myctype AS (a int4, b int4, c varchar) Ideally, what I'm looking for will work in plpgsql, but I'm ok with writing a bit or two in C as long as it can be made short, selfcontained, and bugfree (crashing PostgreSQL or wrong data would be enough rope to hang myself on I'm afraid). BTW, I don't see arrays of composite types in the TODO, and the ability to specify composite types indirectly through schema.rel.attr%TYPE isn't there either. Are these two features out of the question for some reason? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] arrays, composite types
# [EMAIL PROTECTED] / 2005-09-11 12:11:39 -0400:
Roman Neuhauser [EMAIL PROTECTED] writes:
I'm looking for an equivalent of my_composite_type[] for use as a
parameter of a pl/pgsql function. What do people use to dodge this
limitation?
Background: I have a few plpgsql functions that basically accept an
array of objects decomposed into arrays of the objects' attributes:
What do you want to do with these arrays? Why do you want to work with
them in plpgsql?
See this pseudocode, demonstrating the desired implementation:
CREATE DOMAIN cksum_d AS VARCHAR(n)
CONSTRAINT dom_cksum CHECK (VALUE IN ('CRC32', ...));
CREATE TYPE cksum_t AS (
cktype myschema.cksum_d,
ckval INTEGER
);
CREATE TYPE fprops AS (
bytes INTEGER,
cksum myschema.cksum_t,
path VARCHAR(n)
...
);
CREATE TABLE filesets (
id SERIAL,
...
);
CREATE TABLE files (
id SERIAL,
setid INTEGER NOT NULL,
props fprops
FK setid - filesets.id
);
Now I need to replace one or more records in files with a different
one. That's done with:
CREATE FUNCTION replace_files(int, int, fprops[])
RETURNS INTEGER VOLATILE STRICT AS
'DECLARE
_setidALIAS FOR $1;
_arrszALIAS FOR $2;
_newfiles ALIAS FOR $3;
_cnt INTEGER DEFAULT 1;
BEGIN
DELETE FROM files where setid = _setid;
WHILE _cnt = _arrsz LOOP
INSERT INTO files (setid, props) VALUES (_setid, _newfiles[_cnt]);
_cnt := _cnt + 1;
END LOOP;
END;
';
Except the function actually does more, and contains (should
contain)
PERFORM SELECT other_function(_setid, _newfiles[_cnt]);
or similar, and there's a handful of functions that the values pass
through. As it is, I need to change the signature and body of all
these functions whenever I need to add another field to the
(effective) structure files, and I of course want to avoid that.
It's just like passing pointers to structures as function arguments
in C, this helps preserve source code compatibility.
I have working code, it's just ugly:
CREATE FUNCTION replace_files(int, int, varchar[], int[], varchar[], ...)
RETURNS INTEGER VOLATILE STRICT AS
'DECLARE
_setidALIAS FOR $1;
_arrszALIAS FOR $2;
_cktypes ALIAS FOR $3;
_ckvals ALIAS FOR $4;
_pathsALIAS FOR $5;
_cnt INTEGER DEFAULT 1;
DELETE FROM files where setid = _setid;
WHILE _cnt = _arrsz LOOP
INSERT INTO files (setid, props)
VALUES (_setid, _cktypes[_cnt], _ckvals[_cnt], _paths[_cnt], ...);
_cnt := _cnt + 1;
END LOOP;
END;
';
When you get to this point I think I would start looking at using plperl
I'd like to avoid switching to a big language: it's quite late in
the release cycle, and this is a commercial product. I cannot tell
our sales the next version will be three or four months late.
and using Dumper to store the objects in a text column. You're trading
off database normalization against being able to express arbitrarily
complex data structures.
That doesn't fit my needs at all, but thanks for thinking about my
problem!
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] constraints on composite types
This fails on 8.0.3 (syntax error at or near . at character): CREATE TYPE ct AS ( foo INTEGER, bar INTEGER ); CREATE TABLE t1 ( attr ct, CONSTRAINT uq UNIQUE (attr.foo) ); Should it be possible? From reading http://www.postgresql.org/docs/current/static/rowtypes.html it looks like almost everything else works. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] constraints on composite types
# [EMAIL PROTECTED] / 2005-09-09 09:10:30 -0600: On Fri, Sep 09, 2005 at 10:39:58AM -0400, Tom Lane wrote: I don't believe you need the function -- this should be enough: CREATE UNIQUE INDEX t1_b_uniq ON t1 ((attr.foo)); I was expecting that to work too, but it doesn't: ERROR: relation attr does not exist The manual says something to the effect of (table.col).subcol, I'll need that schema-qualified as well, IOW (schema.table.col).subcol -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Acting on dropped/timed-out connections
# [EMAIL PROTECTED] / 2005-09-08 11:31:25 +0300: We have a daemon programme that acts as a pgsql client. It writes in a DB the status of its own clients. And we have a different daemon that needs to read that status information and decide upon it. The problem is that the first daemon is a little fragile and from time to time it crashes. I need to be able to monitor its connection to the PgSQL and if it drops (times-out) to mark the status of all of its clients as unavailable or unknown. Do you prefer knowing that the daemon failed, or having it automaticly restarted the very same moment it crashes? If the latter, take a look at djb's daemontools, or one of its open source lookalikes (http://smarden.org/runit/, http://offog.org/code/freedt.html). -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Email Verfication Regular Expression
# [EMAIL PROTECTED] / 2005-09-07 11:17:10 -0400:
Does anybody have regular expression handy to verfiy email addresses?
This is what I have. The comment notes the caveats.
-- CREATE FUNCTION IS_EMAILADDRESS {{{
-- returns TRUE if $1 matches the rules for RFC2822 addr-spec token,
-- ignoring CFWS in atoms, obs- versions of everything, !dot-atom
-- versions of local-part, and quoted-pairs in domain-literal (IOW,
-- this function doesn't allow backslashes after the @)
-- FIXME: locale-dependent (relies on ranges [x-y])
/*
atext = ALPHA / DIGIT / ; Any character except controls,
! / # / ; SP, and specials.
$ / % / ; Used for atoms
/ ' /
* / + /
- / / /
= / ? /
^ / _ /
` / { /
| / } /
~
dot-atom-text = 1*atext *(. 1*atext)
dot-atom= [CFWS] dot-atom-text [CFWS]
addr-spec = local-part @ domain
local-part = dot-atom / quoted-string / obs-local-part
domain = dot-atom / domain-literal / obs-domain
domain-literal = [CFWS] [ *([FWS] dcontent) [FWS] ] [CFWS]
dcontent= dtext / quoted-pair
dtext = NO-WS-CTL / ; Non white space controls
%d33-90 / ; The rest of the US-ASCII
%d94-126; characters not including [,
; ], or \
NO-WS-CTL = %d1-8 / ; US-ASCII control characters
%d11 / ; that do not include the
%d12 / ; carriage return, line feed,
%d14-31 / ; and white space characters
%d127
*/
CREATE OR REPLACE FUNCTION IS_EMAILADDRESS(VARCHAR)
RETURNS BOOL
IMMUTABLE
RETURNS NULL ON NULL INPUT
LANGUAGE plpgsql
AS '
BEGIN
RETURN $1 ~ ''(?x) # this is an ARE
# local-part dot-atom-text (1*atext)
^[-!#$%*+/=?^_`{|}~[:alnum:]]+
# local-part dot-atom-text (*(. 1*atext))
(?:\.[-!#$%*+/=?^_`{|}~[:alnum:]]+)*
# literal @
@
(?:
# domain (dom-atom or domain-literal)
(?:
# domain dot-atom (1*atext)
[-!#$%*+/=?^_`{|}~[:alnum:]]+
# domain dot-atom (*(. 1*atext))
\.[-!#$%*+/=?^_`{|}~[:alnum:]]+
)*
|
# domain domain-literal ([)
[[]
# domain domain-literal (dcontent)
# ^@-^H ^K ^L ^N ^_ ! -
Z^ - DEL
[x01-x08x0Bx0Cx0E-x1Fx21-x5Ax5E-x7F]*
# domain domain-literal (])
[]]
)
$'';
END;
';
-- }}}
-- CREATE DOMAIN emailaddrspec {{{
CREATE DOMAIN emailaddrspec AS VARCHAR
CONSTRAINT dom_emailaddrspec CHECK (
VALUE = ''
OR IS_EMAILADDRESS(VALUE)
);
-- }}}
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] How to write jobs in postgresql
# [EMAIL PROTECTED] / 2005-08-15 20:25:20 -0500: On Tue, Aug 09, 2005 at 03:26:27PM -0500, Guy Rouillier wrote: chiranjeevi.i wrote: Hi Team Members, Is it possible to write jobs in postgresql if possible how should I write .please help me. See pgjob in pgfoundry: http://pgfoundry.org/projects/pgjob/. It's in the planning stages. Actually, it's currently in the going nowhere stage since no one's expressed any interest in it. Anyone who's interested is encouraged to join the mailing list and post what they'd like to see from the project. What's the advantage over system-native (cron etc) means? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] SLOOOOOOOW
# [EMAIL PROTECTED] / 2005-09-05 09:39:47 +0200: I working now for a wile with postgres (7.4), and I have the impression that is one of the slowest dbms with which I've aver worked. Can please somebody explain to me, why this is the case? Because the default configuration (is | seems to be) aimed at PDAs. You'll need to configure PostgreSQL for real world use. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How do I copy part of table from db1 to db2 (and rename the columns)?
# [EMAIL PROTECTED] / 2005-08-31 12:00:30 +0200: I want to copy several columns of a source table from db1 to db2, and create the target table and rename the columns in the process. Is that possible in PostgresQL? If so, an example or url for such a command /script would be appreciated... check these man pages: pg_dump(1), pg_restore(1), alter_table(7) -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] OTICE: adding missing FROM-clause entry for table
# [EMAIL PROTECTED] / 2005-08-12 10:17:50 +0200: I just experienced some bad SQL causing quite unexpected results. I used a statement like this: SELECT t1.a, t1.b, t2.d FROM test1 t1, test2 t2 WHERE t1.a = test2.a; Where I should have used this instead: SELECT t1.a, t1.b, t2.d FROM test1 t1, test2 t2 WHERE t1.a = t2.a; When I looked into it and tried it from psql, I got this notice: NOTICE: adding missing FROM-clause entry for table test2 Now, I understand that postgresql is adding test2 to the list of tables, I am selecting from as it is missing. However the result is quite different from what I expected. Is there a way to change this behaviour to generate an error instead of just a notice? set add_missing_from = false in postgresql.conf -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] regarding isolation between threads
# [EMAIL PROTECTED] / 2005-08-11 16:04:00 +0530:
void *connect(void* threadid)
{
char command[100];
int *id_ptr, taskid;
id_ptr = (int *) threadid;
taskid = *id_ptr;
if(taskid == 0)
strcpy(command, select insert (1));
else if(taskid == 1)
strcpy(command, select insert (1));
else if(taskid == 2)
strcpy(command, select insert (3));
else if(taskid == 3)
strcpy(command, select insert (4));
else if(taskid == 4)
strcpy(command, select insert (5));
PGconn *conn = connect(dbname=x host=y user=z);
pgresult res;
res = pqexec (conn, begin transaction);
res = pqexec (conn, command);
res = pqexec (conn, commit);
pqfinish (conn);
pthread_exit(NULL);
}
int main()
{
pthread_t threads[NUM_THREADS];
int rc;
int *taskids[NUM_THREADS];
for(int t=0; tNUM_THREADS; t++)
{
taskids[t] = (int *) malloc(sizeof(int));
*taskids[t] = t;
rc = pthread_create(threads[t], NULL, connect, (void *) taskids[t]);
if (rc)
{
printf(ERROR; return code from pthread_create() is %d\n, rc);
exit(-1);
}
}
for(int t=0; tNUM_THREADS; t++)
{
delete taskids[t];
}
pthread_exit(NULL);
}
the stored procedure (just the pseudo code)
table x has a primary key k
insert(integer)
{
select from table if k = $1
if not found
insert into x ($1);
else
insert into some_other_table($1);
end if
}
the kind of output i am expecting is:
table x: 1 3 4 5
table a: 1
and no error message
but the output is something like
table x : 1 3 4 5
table some_other_table :
it has nothing
and error message is displayed :
error in stored proc insert(. primary key violation ..
this error is because two threads are simultaneoulsy trying to insert
the values 1 each and thats where they interfere with each other.
It's doing exactly what you told it to. Thread 1 goes
SELECTing WHERE k = $1, but sees nothing because that row hasn't
been committed yet by thread 0, then thread 0 COMMITs, and thread1
blows up. Or they swap roles, but that doesn't matter.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
# [EMAIL PROTECTED] / 2005-08-11 13:23:52 -0700: I'm getting this warning in pgsql's log: LOG: plphp: PHP Warning: Call-time pass-by-reference has been deprecated - argument passed by value; If you would like to pass it by reference, modify the declaration of [runtime function name](). If you would like to enable call-time pass-by-reference, you can set allow_call_time_pass_reference to true in your INI file. However, future versions may not support this any longer. in plphp trigger call on line 1 Is there anything I can do about it? Yes. It's completely off topic here, however. You'll find more help in the PHP manual and/or php-general@lists.php.net (you can subscribe from http://www.php.net/). -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
# [EMAIL PROTECTED] / 2005-08-11 15:45:18 -0700:
Roman Neuhauser wrote:
# [EMAIL PROTECTED] / 2005-08-11 13:23:52 -0700:
I'm getting this warning in pgsql's log:
LOG: plphp: PHP Warning: Call-time pass-by-reference
has been deprecated - argument passed by value; If
you would like to pass it by reference, modify the
declaration of [runtime function name](). If you
would like to enable call-time pass-by-reference, you
can set allow_call_time_pass_reference to true in your
INI file. However, future versions may not support
this any longer. in plphp trigger call on line 1
Is there anything I can do about it?
Yes.
Actually the below is incorrect. He should be visiting
plphp.commandprompt.com and signing up for the list there.
Why? What does the generic warning emitted by PHP 4 (no need to get
PostgreSQL into the mix) since forever on code like this:
function foo($arg) {} /* foo is declared to take $arg by value */
foo($var); /* $var is passed by reference */
have to do with PL/PHP?
It's completely off topic here, however. You'll find more help in
the PHP manual and/or php-general@lists.php.net (you can subscribe
from http://www.php.net/).
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Long running update
# [EMAIL PROTECTED] / 2005-08-11 16:17:09 -0700: Hello, I currently running an update statement that updates every row in a very large table. This query will obviously take a long time to run. My question -- is there any way to know how much time it will take once it starts? Even something that could help me approximate the speed at which it's doing the update would be helpful. Yeah, it would be nice if select, insert, update, delete could be set to log their progress at configured intervals. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
# [EMAIL PROTECTED] / 2005-08-11 16:49:25 -0700: I'm using PHP5, and I'm not passing by reference. My first stop WAS plphp.commandprompt.com, but none of their mailing list links for plphp work. Can you post the code that triggers the warning? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] plphp: PHP Warning: Call-time pass-by-reference has been deprecated
# [EMAIL PROTECTED] / 2005-08-11 17:36:49 -0700: --- Roman Neuhauser [EMAIL PROTECTED] wrote: Can you post the code that triggers the warning? Sure- CREATE or REPLACE FUNCTION email_activated_member () RETURNS trigger AS $$ $new=$_TD['new']; $old=$_TD['old']; if(($_TD['event']=='INSERT' and $new['active']='t') or You are assigning to $new['active'] instead of the probably wanted comparison. I don't see any byref arguments, and don't know how to help further. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Error Loading postgresql
# [EMAIL PROTECTED] / 2005-08-06 13:37:45 +0530: errors while loading in redhat8.0 linux [EMAIL PROTECTED] postgrep]# ls postgresql-8.0.3 postgresql-8.0.3.tar.bz2 [EMAIL PROTECTED] postgrep]# cd postgresql-8.0.3 [EMAIL PROTECTED] postgresql-8.0.3]# ./configure checking build system type... i686-pc-linux-gnulibc1 checking host system type... i686-pc-linux-gnulibc1 checking which template to use... linux checking whether to build with 64-bit integer date/time support... no checking whether NLS is wanted... no checking for default port number... 5432 checking for gcc... gcc checking for C compiler default output... configure: error: C compiler cannot create executables [EMAIL PROTECTED] postgresql-8.0.3]# configure creates config.log which is bound to contain the reason for the failure you observed. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] 5 new entries for FAQ
# kleptog@svana.org / 2005-08-10 10:02:20 +0200: After going through pgsql-general a bit I figured there were a few important questions missing from the FAQ, so I wrote some. Comments welcome. I can write more, if people can suggest things to write about. I was thinking something about collation and locales but I'm sure sure I understand them myself. +H3A name=4.224.22/A) Why are PostgreSQL table names case-sensitive?/H3 http://www.postgresql.org/docs/current/static/features.html doesn't contain fold (as in case folding) at all, doesn't this topic belong rather there? The FAQ entry could point to, and maybe extend, the (still nonexistent) text in that document. +H3A name=4.234.23/A) Why is PostgreSQL only using one CPU to execute my query?/H3 Something like this is missing from http://www.postgresql.org/docs/current/static/overview.html. +H3A name=4.254.25/A) What does 'index row size ... exceeds btree maximum, 2713' mean?/H3 + +PUnlike some databases, PostgreSQL allows you to create an index on +any column, including unlimited text fields. However, B-Tree indexes +need to be able to hold at least three key values per page and since the +pagesize defaults to 8K minus some overhead, this means the maximum key +size is 2713 bytes. This is missing from http://www.postgresql.org/docs/current/static/sql-createindex.html and/or http://www.postgresql.org/docs/current/static/indexes.html -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Case sensitivity
# [EMAIL PROTECTED] / 2005-08-10 13:18:32 +0200: 1. Will SELECT WHERE LOWER(colname) = 'a001' use the index, or must I create a separate index on LOWER(colname)? the latter -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] 5 new entries for FAQ
# kleptog@svana.org / 2005-08-10 11:22:16 +0200: On Wed, Aug 10, 2005 at 10:44:14AM +0200, Roman Neuhauser wrote: +H3A name=4.224.22/A) Why are PostgreSQL table names case-sensitive?/H3 http://www.postgresql.org/docs/current/static/features.html doesn't contain fold (as in case folding) at all, doesn't this topic belong rather there? The FAQ entry could point to, and maybe extend, the (still nonexistent) text in that document. Well, it's not a feature, so I'm not sure why it would be mentioned there. It's PostgreSQL's implementation of the standard. That page has this heading: Appendix D. SQL Conformance It is all explained it detail in: http://www.postgresql.org/docs/8.0/static/sql-syntax.html#SQL-SYNTAX-IDENTIFIERS Seeing as the last para in 4.1.1. Identifiers and Key Words seems to be almost exactly what you came up with (not accusing you of plagiarism or such, the wording is obvious, and different people will arrive at virtually identical explanations), wouldn't just linking there suffice? (I'm a bit worried about information scatter and duplication). It's just that the question as stated comes up regularly and in this form, which is why I did that. Perhaps a link would be a good idea. Yes, that question is quite common, and perfectly answered in the documentation; I would just point at it from the FAQ. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cursor Issue??
# [EMAIL PROTECTED] / 2005-08-04 09:56:03 -0700: Thanks Roman for sticking with me on this! For whatever reason I cannot load another langage, I think it has to do with recompiling the program and installing all the options. Not sure though?? LANGUAGE plpgsql doesn't exist for me. Please send the output of this command: createlang -U postgres -d $dbname plpgsql I still find this cursor limitation wacked. I find it hard to believe that nobody else is requiring this curosr funcionality. Why else have a cursor? You are trying to use a cursor in interactive SQL. SQL99 doesn't allow that at all! See the bottom from this link http://www.postgresql.org/docs/7/interactive/sql-fetch.htm Even in there is display of a teaser that it can be done. Too bad there is no example. It says: : Compatibility : SQL92 : : Note: The non-embedded use of cursors is a Postgres extension. : The syntax and usage of cursors is being compared against the : embedded form of cursors defined in SQL92. : : SQL92 allows absolute positioning of the cursor for FETCH, and : allows placing the results into explicit variables. : : FETCH ABSOLUTE # : FROM cursor : INTO :variable [, ...] The text above is valid for SQL:1999 as well. You fall in the non-embedded category, IOW that insufficient functionality you are trying to use is outside the SQL standard. The example servers to document the paragraph that begins SQL92 allows (...), IOW what you see is the SQL92, Embedded SQL syntax, not what you can do in PostgreSQL. My objective is to look at each record one at at time from top to bottom. I need to take that information in variable form, and run it through a routine that is in the cursor block, then the end result needs to end up in another table. There will be times where I will also need to scroll forward and backward. To me the fact that the interactive FETCH can only *display* the row, while PL/PGSQL is always NO SCROLL and you can only FETCH NEXT on it, looks like a bad combination of features and shortcomings. Maybe you'd like to take this to the pgsql-docs@ list? At least the more knowledgable people could add some real life (= nontrivial) examples. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Cursor Issue??
# [EMAIL PROTECTED] / 2005-08-02 10:01:34 -0400: I made it happen in MicrosoftSQL using the first code below. The only difference is I had to create variables. Which I'm having a hard time trying to replicate it in psql. __Microsoft Code___ USE test GO DECLARE @qty INT, @Length varchar(20), @Width varchar(40) DECLARE cursor1 SCROLL CURSOR FOR SELECT * from Parts OPEN cursor1 FETCH FIRST FROM cursor1 INTO @qty, @Length, @Width INSERT INTO PartsTemp (qty, Length, Width) VALUES (@qty, @Length, @Width) CLOSE cursor1 DEALLOCATE cursor1 GO The code above puts a single, randomly chosen tuple from Parts into PartsTemp. If that's all you need, you can do it with: INSERT INTO PartsTemp (SELECT * FROM Parts LIMIT 1); __psql Code___ (These declaration of vaiables don't work) DECLARE c_qty INT; DECLARE c_Length FLOAT; DECLARE c_Width FLOAT; BEGIN; DECLARE cursor1 CURSOR FOR SELECT * FROM Parts; FETCH FIRST FROM cursor1 INTO c_qty, c_Length, c_Width; INSERT INTO partstemp VALUES (c_qty, c_Length, c_Width); CLOSE cursor1; COMMIT; Got any ideas using variable to transfer singular rows? If you need to do more (you aren't telling much), and want/need to use cursors, you'll have to resort to using PL/pgSQL. This hack would do it: CREATE TABLE t1 (t1i INT, t1c CHAR(1)); CREATE TABLE t2 (t2i INT, t2c CHAR(1)); INSERT INTO t1 VALUES (1, 'a'); INSERT INTO t1 VALUES (2, 'b'); INSERT INTO t1 VALUES (3, 'c'); CREATE FUNCTION do_it() RETURNS BOOLEAN VOLATILE LANGUAGE plpgsql AS ' DECLARE _ti INTEGER; _tc CHAR(1); _c1 CURSOR FOR SELECT t1i, t1c FROM t1; BEGIN OPEN _c1; FETCH _c1 INTO _ti, _tc; INSERT INTO t2 VALUES (_ti, _tc); CLOSE _c1; RETURN TRUE; END; '; SELECT do_it(); DROP FUNCTION do_it(); But watch out, because PL/pgSQL doesn't provide a way to create SCROLLable cursors, FETCH more than one tuple at a time, or FETCH orientation. I urge you to read about functions and PL/pgSQL in the manual: http://www.postgresql.org/docs/current/static/server-programming.html http://www.postgresql.org/docs/current/static/plpgsql.html http://www.postgresql.org/docs/current/static/sql-createfunction.html and note that SQL DECLARE is a different beast from PL/pgSQL DECLARE, etc for other statements. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Cursor Issue??
# [EMAIL PROTECTED] / 2005-07-27 12:21:34 -0700:
I found using the shell works but using the phAdminIII GUI is the one
that gives me problems. I've even tried running it on EMS PostgreSQL
Manager 3. Same results. Is this normal?
Got a couple more questions regarding cursors.
1. When I try to run this statement (Declare curs1 refcursor;) I get
an error ERROR: syntax error at or near refcursor at character 23
Have you seen the answer to this I sent you in my reply to your
offlist email? If not, reread the mail. If yes, and you still don't
see the problem: which part of
DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]
CURSOR [ { WITH | WITHOUT } HOLD ] FOR query
[ FOR { READ ONLY | UPDATE [ OF column [, ...] ] } ]
does allow for DECLARE curs1 refcursor? The answer is simple:
none. You're trying to use a plpgsql declaration outside plpgsql.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgresql with max_connections=4096
# [EMAIL PROTECTED] / 2005-07-27 10:22:48 +0200: Richard Huxton wrote: Sounds like a BEGIN being re-issued alright. Solution - fix your application(s) and don't use persistent connections (or if you do, make sure you rollback any pre-existing transactions and issue any relevant SET commands). If that was the problem, I should obtain always that error but I obtain that error only after two/three hours of testing. Not necessarily. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Daily digest?
# [EMAIL PROTECTED] / 2005-07-27 08:56:24 -0400: How does one receive all mail to this list in a daily digest? Have you read the mailing list usage notes on the web site? -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Daily digest?
# [EMAIL PROTECTED] / 2005-07-27 10:03:15 -0400: Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2005-07-27 08:56:24 -0400: How does one receive all mail to this list in a daily digest? Have you read the mailing list usage notes on the web site? set pgsql-general digest Yes, that's the command you should send (elsewhere). -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Cursor Issue??
# [EMAIL PROTECTED] / 2005-07-26 14:06:34 -0700:
BEGIN WORK;
DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
FETCH FIRST FROM cursor1;
CLOSE cursor1;
COMMIT WORK;
Query result with 1 rows discarded.
Query returned successfully with no result in 31 ms.
In the data output view nothing is returned?
Complain to your 'data output view' vendor.
test=# create table parts (id serial, t text);
CREATE TABLE
test=# insert into parts (t) values ('aaa');
INSERT 72423 1
test=# insert into parts (t) values ('bbb');
INSERT 72424 1
test=# insert into parts (t) values ('ccc');
INSERT 72425 1
test=# select * from parts;
id | t
+-
1 | aaa
2 | bbb
3 | ccc
(3 rows)
test=# BEGIN WORK;
BEGIN
test=# DECLARE cursor1 CURSOR FOR SELECT * FROM PARTS;
DECLARE CURSOR
test=# FETCH FIRST FROM cursor1;
id | t
+-
1 | aaa
(1 row)
test=# CLOSE cursor1;
CLOSE CURSOR
test=# COMMIT WORK;
COMMIT
test=#
As you can see, the fetched row is displayed just fine.
--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man. You don't KNOW.
Cause you weren't THERE. http://bash.org/?255991
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] About Tools at the DB design phase.
# [EMAIL PROTECTED] / 2005-07-27 10:05:43 -0400: Greetings, I am at the design phase of the DB design. That is, I'd like to design tables and relationships between them, but not the real implement of tables. Could somebody suggest some good and free tools to help/ease design the structures please? They're not exactly free, but a pen and a hardback notebook (A4) have proven to be the best tools over the time. YMMV. -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] About Tools at the DB design phase.
# [EMAIL PROTECTED] / 2005-07-27 10:13:10 -0700: On Wed, 27 Jul 2005, Roman Neuhauser wrote: # [EMAIL PROTECTED] / 2005-07-27 10:05:43 -0400: I am at the design phase of the DB design. That is, I'd like to design tables and relationships between them, but not the real implement of tables. Could somebody suggest some good and free tools to help/ease design the structures please? They're not exactly free, but a pen and a hardback notebook (A4) have proven to be the best tools over the time. YMMV. Heh, I have to concur with this comment. Though I always found the US letter format to be more standards compliant, myself. This is the Central Europe, sir. You either play by the continental standards or we send you back home. ;) -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] back-end triggers front-end to update
# [EMAIL PROTECTED] / 2005-07-26 18:25:23 -0300: htmldiv style='background-color:'DIV class=RTEHello everyone,BRI am searching for a way to have my postgresql 7.4.7 backend be triggered to let the front end know there has been a change to the database. If more then one person is connected to the database and person (x) makes a change, I want other clients to then be aware of that, and refresh there screen so they are not then looking at out of date data. BRAny insight?nbsp; Thank you so much, have a great day.BRAdam O'Toole /DIV/div/html Please keep HTML out of email unless necessary to convey structure and/or meaning. See LISTEN(7) and NOTIFY(7). -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Rules vs Triggers
# [EMAIL PROTECTED] / 2005-07-26 17:53:35 -0400: Read the Rules section of the manual and the section on Rules vs Triggers. From what I get triggers are necessary for column constraints. As far as speed, it seems there are some differences between how fast rules/triggers would do the same action, but that some complex analysis is involved to determine this. And I gathered rules are necessary to allow update/insert/delete actions on views. Can anyone give me some simple reasons why they choose rules over triggers in their real-world dbs? Something like this will ensure the user will not be able to modify the author information in updatedon/updatedby columns: CREATE TABLE t1 ( id SERIAL, val TEXT, updatedon TIMESTAMP, updatedby TEXT ); CREATE VIEW v1 AS SELECT * FROM t1; CREATE RULE v1i AS ON INSERT TO v1 DO INSTEAD INSERT INTO t1 (val, updatedon, updatedby) VALUES (NEW.val, NOW(), CURRENT_USER); CREATE RULE v1u AS ON UPDATE TO v1 DO INSTEAD UPDATE t1 SET val = NEW.val, updatedon = NOW(), updatedby = CURRENT_USER WHERE id = NEW.id; (That should be taken as pseudocode, I'm sure there are bugs in it.) Another common reason is the need/desire to keep values of certain columns somehow synchronized, as in: CREATE FUNCTION UNIXTS_TO_SQLTS(INTEGER) RETURNS TIMESTAMP AS ...; CREATE TABLE t2 ( id SERIAL, unixts INTEGER, sqlts TIMESTAMP ); CREATE VIEW v2 AS SELECT * FROM t2; CREATE RULE v2i AS ON INSERT TO v2 DO INSTEAD INSERT INTO t2 (unixts, sqlts) VALUES (NEW.unixts, UNIXTS_TO_SQLTS(NEW.unixts); CREATE RULE v2u AS ON UPDATE TO v2 DO INSTEAD UPDATE t2 SET unixts = NEW.unixts, sqlts = UNIXTS_TO_SQLTS(NEW.unixts), WHERE id = NEW.id; So basically, it's these reasons: * to have updatable views - so you don't select from view_x, but insert into table_x; - if updating certain view involves updating more than one table, you'll want to have the code fixated in a rule to tighten the space where clients can screw up * to prevent clients from updating certain columns and/or rows * to enforce certain characteristics of data -- How many Vietnam vets does it take to screw in a light bulb? You don't know, man. You don't KNOW. Cause you weren't THERE. http://bash.org/?255991 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
