Re: [GENERAL] Trigger (Calling a Procedure)
Its actually something like the trigger should start a C function after insert and the C function has the ECPG code for some more inserts. Its similar to the way we dynamically load a shared library while executing a stored procedure, as in , executing a fucntion in C file using stored procedure/ function. Harpreet On 8/16/06, Harpreet Dhaliwal <[EMAIL PROTECTED]> wrote: I got your point. however, my requirement is something like this. The trigger shold start another function (a stored procedure) after any event is fired. how do I accomplish this goal? Harpreet On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Wed, Aug 16, 2006 at 01:35:47AM -0400, Harpreet Dhaliwal wrote:> I'm trying to create a trigger with the following definition: >> CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON> raw_email> FOR EACH ROW EXECUTE PROCEDURE add_one(1);>> Procedure / Function add_one has the following definition >> CREATE FUNCTION add_one(integer) RETURNS integer> AS '/usr/local/pgsql/jsb/add_one', 'add_one'> LANGUAGE C STRICT;>> function add_one is running fine.>> When I try to create the trigger insert_price_change, it throws me the > follwoing error:>> ERROR: function add_one() does not existTrigger functions must return type "trigger" and they must bedeclared with no arguments. You can pass an argument as in your CREATE TRIGGER statement but a trigger function receives its argumentsdifferently than an ordinary function. See the Triggers chapterin the documentation, especially "Writing Trigger Functions in C": http://www.postgresql.org/docs/8.1/interactive/triggers.html Unless you need to use C I'd suggest using PL/pgSQL. Even if youdo need to use C I'd recommend practicing with PL/pgSQL to learn the basics with a simpler language. http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html--Michael Fuhr
Re: [NOVICE] [GENERAL] DB insert Error
Its actually something like the trigger should start a C function after insert and the C function has the ECPG code for some more inserts. Its similar to the way we dynamically load a shared library while executing a stored procedure, as in , executing a fucntion in C file using stored procedure/ function. Harpreet On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote:> I changed the datatype from varchar[] to varchar > ECPGdebug(1,stderr) says> [2998]: ECPGexecute line 97 Ok: INSERT 0 1>> Its not inserting any record even though sqlcode is 0.Are you committing the transaction? See the bottom of the following page:http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html"In the default mode, statements are committed only when EXEC SQL COMMIT is issued."--Michael Fuhr---(end of broadcast)---TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Trigger (Calling a Procedure)
I got your point. however, my requirement is something like this. The trigger shold start another function (a stored procedure) after any event is fired. how do I accomplish this goal? Harpreet On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Wed, Aug 16, 2006 at 01:35:47AM -0400, Harpreet Dhaliwal wrote:> I'm trying to create a trigger with the following definition: >> CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON> raw_email> FOR EACH ROW EXECUTE PROCEDURE add_one(1);>> Procedure / Function add_one has the following definition >> CREATE FUNCTION add_one(integer) RETURNS integer> AS '/usr/local/pgsql/jsb/add_one', 'add_one'> LANGUAGE C STRICT;>> function add_one is running fine.>> When I try to create the trigger insert_price_change, it throws me the > follwoing error:>> ERROR: function add_one() does not existTrigger functions must return type "trigger" and they must bedeclared with no arguments. You can pass an argument as in your CREATE TRIGGER statement but a trigger function receives its argumentsdifferently than an ordinary function. See the Triggers chapterin the documentation, especially "Writing Trigger Functions in C": http://www.postgresql.org/docs/8.1/interactive/triggers.htmlUnless you need to use C I'd suggest using PL/pgSQL. Even if youdo need to use C I'd recommend practicing with PL/pgSQL to learn the basics with a simpler language.http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html--Michael Fuhr
Re: [NOVICE] [GENERAL] DB insert Error
On Wed, Aug 16, 2006 at 01:46:30AM -0400, Jasbinder Bali wrote: > I changed the datatype from varchar[] to varchar > ECPGdebug(1,stderr) says > [2998]: ECPGexecute line 97 Ok: INSERT 0 1 > > Its not inserting any record even though sqlcode is 0. Are you committing the transaction? See the bottom of the following page: http://www.postgresql.org/docs/8.1/interactive/ecpg-commands.html "In the default mode, statements are committed only when EXEC SQL COMMIT is issued." -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Trigger (Calling a Procedure)
On Wed, Aug 16, 2006 at 01:35:47AM -0400, Harpreet Dhaliwal wrote: > I'm trying to create a trigger with the following definition: > > CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON > raw_email > FOR EACH ROW EXECUTE PROCEDURE add_one(1); > > Procedure / Function add_one has the following definition > > CREATE FUNCTION add_one(integer) RETURNS integer > AS '/usr/local/pgsql/jsb/add_one', 'add_one' > LANGUAGE C STRICT; > > function add_one is running fine. > > When I try to create the trigger insert_price_change, it throws me the > follwoing error: > > ERROR: function add_one() does not exist Trigger functions must return type "trigger" and they must be declared with no arguments. You can pass an argument as in your CREATE TRIGGER statement but a trigger function receives its arguments differently than an ordinary function. See the Triggers chapter in the documentation, especially "Writing Trigger Functions in C": http://www.postgresql.org/docs/8.1/interactive/triggers.html Unless you need to use C I'd suggest using PL/pgSQL. Even if you do need to use C I'd recommend practicing with PL/pgSQL to learn the basics with a simpler language. http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html -- Michael Fuhr ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DB insert Error
Also,
My insert statement is
EXEC SQL INSERT INTO raw_email (id,raw_email) VALUES (1,:ch);
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 01:20:08AM -0400, Jasbinder Bali wrote:> This is how the array is formed in my C code
> ---> FILE *fp;>> while(!feof(fp))> {ch[i]=fgetc(fp);> if(ch[i]=='\n') lines++; i++; }
>ch[i-1]='\0';>fclose(fp);> --> and then am inserting ch as a whole in the varchar column in the database.
>> Do you want me to append a leading '{' and a trailing '}' to ch??Do you intend to handle the data as an array in SQL? If not thenthe raw_email column should be declared as varchar or text instead
of varchar[]. In that case your C code won't need to change.--Michael Fuhr
Re: [GENERAL] DB insert Error
I changed the datatype from varchar[] to varchar
ECPGdebug(1,stderr) says
[2998]: ECPGexecute line 97 Ok: INSERT 0 1
Its not inserting any record even though sqlcode is 0.
~Jas
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 01:20:08AM -0400, Jasbinder Bali wrote:> This is how the array is formed in my C code
> ---> FILE *fp;>> while(!feof(fp))> {ch[i]=fgetc(fp);> if(ch[i]=='\n') lines++; i++; }
>ch[i-1]='\0';>fclose(fp);> --> and then am inserting ch as a whole in the varchar column in the database.
>> Do you want me to append a leading '{' and a trailing '}' to ch??Do you intend to handle the data as an array in SQL? If not thenthe raw_email column should be declared as varchar or text instead
of varchar[]. In that case your C code won't need to change.--Michael Fuhr
Re: [GENERAL] DB insert Error
On Wed, Aug 16, 2006 at 01:20:08AM -0400, Jasbinder Bali wrote:
> This is how the array is formed in my C code
> ---
> FILE *fp;
>
> while(!feof(fp))
> {ch[i]=fgetc(fp);
> if(ch[i]=='\n') lines++; i++; }
>ch[i-1]='\0';
>fclose(fp);
> --
> and then am inserting ch as a whole in the varchar column in the database.
>
> Do you want me to append a leading '{' and a trailing '}' to ch??
Do you intend to handle the data as an array in SQL? If not then
the raw_email column should be declared as varchar or text instead
of varchar[]. In that case your C code won't need to change.
--
Michael Fuhr
---(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] Trigger (Calling a Procedure)
Hi,I'm trying to create a trigger with the following definition:CREATE TRIGGER insert_price_change AFTER INSERT OR DELETE OR UPDATE ON raw_email FOR EACH ROW EXECUTE PROCEDURE add_one(1);Procedure / Function add_one has the following definition CREATE FUNCTION add_one(integer) RETURNS integer AS '/usr/local/pgsql/jsb/add_one', 'add_one' LANGUAGE C STRICT;function add_one is running fine.When I try to create the trigger insert_price_change, it throws me the follwoing error: ERROR: function add_one() does not existHowever, I can see function add_one(int4) as one of the functions in pgadmin.Don't know whats going on wrong.Thanks,~Harpreet
Re: [GENERAL] DB insert Error
This is how the array is formed in my C code
---
FILE *fp;
while(!feof(fp)) {ch[i]=fgetc(fp); if(ch[i]=='\n') lines++; i++; } ch[i-1]='\0'; fclose(fp);--
and then am inserting ch as a whole in the varchar column in the database.
Do you want me to append a leading '{' and a trailing '}' to ch??
~Jas
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 16, 2006 at 12:38:42AM -0400, Jasbinder Bali wrote:> table definition of raw_email table is as follows
>> CREATE TABLE raw_email (> idint4 NOT NULL,> raw_email varchar[],> parsed_flag bool NOT NULL DEFAULT false,> CONSTRAINT pk_rawemail PRIMARY KEY (id)
> )> WITHOUT OIDS;>> i have very much declared raw_email field as varchar and not charIt's not the varchar-vs-char distinction that matters; it's thefact that raw_email is declared as array, so it expects a certain
input syntax. Example:test=> INSERT INTO raw_email (id, raw_email) VALUES (1, 'test');ERROR: array value must start with "{" or dimension informationtest=> INSERT INTO raw_email (id, raw_email) VALUES (1, '{test}');
INSERT 0 1Are you sure you want raw_email to be an array instead of a simplevarchar column? Are you handling the contents as array elements(e.g., one element per line) or is the whole considered a single
piece of data?--Michael Fuhr
Re: [GENERAL] how to use variable for select query in pl/pgsql
On Wed, Aug 16, 2006 at 12:43:07PM +0800, aBBISh wrote: > i want use a variable to denote a table name in select query. See "Executing Dynamic Commands" in the PL/pgSQL documentation: http://www.postgresql.org/docs/8.1/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN -- Michael Fuhr ---(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 insert Error
On Wed, Aug 16, 2006 at 12:38:42AM -0400, Jasbinder Bali wrote:
> table definition of raw_email table is as follows
>
> CREATE TABLE raw_email (
> idint4 NOT NULL,
> raw_email varchar[],
> parsed_flag bool NOT NULL DEFAULT false,
> CONSTRAINT pk_rawemail PRIMARY KEY (id)
> )
> WITHOUT OIDS;
>
> i have very much declared raw_email field as varchar and not char
It's not the varchar-vs-char distinction that matters; it's the
fact that raw_email is declared as array, so it expects a certain
input syntax. Example:
test=> INSERT INTO raw_email (id, raw_email) VALUES (1, 'test');
ERROR: array value must start with "{" or dimension information
test=> INSERT INTO raw_email (id, raw_email) VALUES (1, '{test}');
INSERT 0 1
Are you sure you want raw_email to be an array instead of a simple
varchar column? Are you handling the contents as array elements
(e.g., one element per line) or is the whole considered a single
piece of data?
--
Michael Fuhr
---(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] how to use variable for select query in pl/pgsql
hello everybody, i want use a variable to denote a table name in select query. for example: DECLARE table_message_data varchar; record_message_data record; BEGIN table_message_data := prefix || 'message_data'; select into record_message_data * from table_message_data; END; the "prefix" is the function input parameter,but i don't know how to use the "table_message_data" in select query. please tell me,thanks. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DB insert Error
table definition of raw_email table is as follows
CREATE TABLE raw_email ( idint4 NOT NULL, raw_email varchar[],
parsed_flag bool NOT NULL DEFAULT false,
CONSTRAINT pk_rawemail PRIMARY KEY (id))
WITHOUT OIDS;
i have very much declared raw_email field as varchar and not char
~Jas
On 8/16/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Tue, Aug 15, 2006 at 11:55:58PM -0400, Jasbinder Bali wrote:> I'm using ECPG and trying to do follwoing insert in one of the talbes.
>> insert into raw_email ( id , raw_email ) values ( 1 , :ch);>> ch is an array and defined as follows:>> EXEC SQL BEGIN DECLARE SECTION;>char ch[MSG_LEN];> EXEC SQL END DECLARE SECTION;
>> contents of array ch is actually a parsed raw email that i've attached> herewith.>> I get the following error when the insert statement is hit:>> [2446]: ECPGexecute line 97: Error: ERROR: array value must start with "{"
> or dimension information.What's the table definition for raw_email? Based on the error itlooks like one of the columns you're inserting is defined as anarray. I'm guessing you did something like this:
CREATE TABLE raw_email ( id integer, raw_email char[]);When you should have done this:CREATE TABLE raw_email ( id integer, raw_email text -- or varchar
);Is my guess correct?--Michael Fuhr
Re: [GENERAL] DB insert Error
On Tue, Aug 15, 2006 at 11:55:58PM -0400, Jasbinder Bali wrote:
> I'm using ECPG and trying to do follwoing insert in one of the talbes.
>
> insert into raw_email ( id , raw_email ) values ( 1 , :ch);
>
> ch is an array and defined as follows:
>
> EXEC SQL BEGIN DECLARE SECTION;
>char ch[MSG_LEN];
> EXEC SQL END DECLARE SECTION;
>
> contents of array ch is actually a parsed raw email that i've attached
> herewith.
>
> I get the following error when the insert statement is hit:
>
> [2446]: ECPGexecute line 97: Error: ERROR: array value must start with "{"
> or dimension information.
What's the table definition for raw_email? Based on the error it
looks like one of the columns you're inserting is defined as an
array. I'm guessing you did something like this:
CREATE TABLE raw_email (
id integer,
raw_email char[]
);
When you should have done this:
CREATE TABLE raw_email (
id integer,
raw_email text -- or varchar
);
Is my guess correct?
--
Michael Fuhr
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] DB insert Error
Hi,
I'm using ECPG and trying to do follwoing insert in one of the talbes.
insert into raw_email ( id , raw_email ) values ( 1 , :ch);
ch is an array and defined as follows:
EXEC SQL BEGIN DECLARE SECTION; char ch[MSG_LEN];EXEC SQL END DECLARE SECTION;
contents of array ch is actually a parsed raw email that i've attached herewith.
I get the following error when the insert statement is hit:
[2446]: ECPGexecute line 97: Error: ERROR: array value must start with "{" or dimension information.
No clue what this error is all about. I googled for this error, found a few results but that didn't help much.
Thanks,
~Jas
From [EMAIL PROTECTED] Tue Apr 15 20:24:47 2003
X-MultiHeader: one
X-MultiHeader: two
X-MultiHeader: three
From: Simon Cozens <[EMAIL PROTECTED]>
To: test
Bcc: [EMAIL PROTECTED]
Subject: foo
Mime-Version: 1.0
Content-Type: image/gif
Content-Disposition: attachment; filename="1.gif"
Content-Transfer-Encoding: base64
X-Operating-System: Linux deep-dark-truthful-mirror 2.4.9
X-POM: The Moon is Waxing Gibbous (98% of Full)
X-Addresses: The [EMAIL PROTECTED] address is deprecated due to being broken.
[EMAIL PROTECTED] still works, but simon-cozens.org or netthink.co.uk are
preferred.
X-Mutt-Fcc: =outbox-200304
Status: RO
Content-Length: 1205
Lines: 17
R0lGODlhDAAMAPcAAAgICBAQEBgYGCkpKTExMTk5OUpKSoyMjJSUlJycnKWlpbW1tc7O
zufn5+/v7/f39///
/ywADAAMAAAIXwAjRICQwIAAAQYUQBAYwUEB
AAACEIBYwMHAhxARNIAIoAAEBBAPOICwkSMCjBAXlKQYgCMABSsjtuQI02UAlC9jFgBJMyYC
CCgRMODoseFElx0tCvxYIEAAAwkWRggIADs=
---(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] Connection string
On Tue, Aug 15, 2006 at 03:56:03PM +0200, Michael Meskes wrote: > On Tue, Aug 15, 2006 at 05:59:23AM -0600, Michael Fuhr wrote: > > I'll submit a patch. However, in the case of string literals not > > working, is that a documentation bug or a code bug? Are they > > supposed to work? > > You shoudl be able to use a string constant or a char * variable as > database name. There are a lot of test cases for connect available under > ecpg/test/connect now. If string literals do not work we have fix it and > add it to the test suite. Will that be a minor fix that can be backpatched or will it be invasive enough to be fixed only in HEAD? I'll submit a documentation patch (or patches for different versions) but I'll need to know how much to change. -- Michael Fuhr ---(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] How to add days to date
On Tue, Aug 15, 2006 at 10:10:27PM -0300, Alejandro Michelin Salomon ( Adinet )
wrote:
> EX :
> '2006-08-01' + 30 + ( 7 * ( 3 - 1 )) ==> '2006-08-01' + 44
>
> All my trys fails.
The error message hints at what's wrong:
test=> SELECT '2006-08-01' + 30 + (7 * (3 - 1));
ERROR: invalid input syntax for integer: "2006-08-01"
PostgreSQL doesn't know that the untyped string is supposed to be
interpreted as a date. Use a cast:
test=> SELECT '2006-08-01'::date + 30 + (7 * (3 - 1));
?column?
2006-09-14
(1 row)
or
test=> SELECT CAST('2006-08-01' AS date) + 30 + (7 * (3 - 1));
?column?
2006-09-14
(1 row)
--
Michael Fuhr
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] How to add days to date
Hi: I have problem trying to add same days to a date. I have this select : SELECT '2006-08-01' + FP.carencia + ( FP.prazo * ( MFP.parcela - 1 )) AS vencimento FROM fi_mov_formas_pagamento MFP LEFT OUTER JOIN fi_formas_pagamento FP ON ( MFP.idformapagamento = FP.idformapagamento AND MFP.idempresa = FP.idempresa ) INNER JOIN hd_cabecalho HDC ON ( MFP.idmovimento = HDC.idhelpdesk AND MFP.idempresa = HDC.idempresa ) WHERE MFP.idmovimento = 1 AND MFP.idempresa = 1 AND MFP.idtipomovimentacao = 1 '2006-08-01' + FP.carencia + ( FP.prazo * ( MFP.parcela - 1 )) --> This results in EX : '2006-08-01' + 30 + ( 7 * ( 3 - 1 )) ==> '2006-08-01' + 44 All my trys fails. Can you help me ? Thanks in advance. Alejandro Michelin Salmon ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] plpgsql dynamic queries and optional arguments
Curtis,
Here is an example function that uses dynamic sql.
I use it under 7.4.5
Hope this helps.
Allan
-- Function to delete old data out of the point tables.
-- tablename is a column in the points table that holds the name
-- of the table in which this points data is stored.
create or replace function delete_old() returns integer as '
declare
pt record;
count integer;
sql_str varchar(512);
begin
count := 0;
for pt in select * from points loop
sql_str := ''deleting from '' || pt.tablename || '' data older than
'' || pt.savefor::varchar || '' days'';
--raise notice ''%'', sql_str;
sql_str := ''delete from '' || pt.tablename || '' where dt < (now()
- interval '' || pt.savefor::varchar || '' days)::timestamp;'';
execute sql_str;
count := count + 1;
end loop;
return count;
end;
' LANGUAGE plpgsql;
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Curtis Scheer
Sent: Wednesday, 16 August 2006 3:22 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] plpgsql dynamic queries and optional arguments
I have a table that I would like to be able to retrieve information out of
based on a combination of multiple columns and I would like to be able to do
this through a plpgsql stored procedure. Right now I have multiple stored
procedures that I am calling based on the values parameter values I pass them
and I am using static sql. The problem with this is it doesn't scale as well as
I would like it to because when I add another column of information to the
table that needs to be used for retrieval it adds another level of combinations.
Also, when dealing with null values with static sql I use the same exact sql
statement except for the where clause containing the "column1 is null" versus
"column1 = passedvalue". Anyways, I have made a simple example procedure and
table; any help would be greatly appreciated basically I would like to use
dynamic sql instead of static but I have unsuccessfully been able to retrieve
the results of a dynamic sql statement in a pgplsql procedure. Here is the
example table and stored procedure.
CREATE TABLE public.foo
(
fooid int4 NOT NULL DEFAULT nextval('foo_fooid_seq'::regclass),
foo_date timestamp NOT NULL,
footypeid int4 NOT NULL,
footext varchar,
CONSTRAINT pk_fooid PRIMARY KEY (fooid)
)
WITHOUT OIDS;
ALTER TABLE public.foo OWNER TO fro;
CREATE OR REPLACE FUNCTION public.get_nextfoo(pfoo_date "timestamp", pfoovalue
int4, pfootext bpchar)
RETURNS SETOF public.foo AS
$BODY$DECLARE
rec foo%ROWTYPE;
BEGIN
if pfootext is null then
SELECT
*
INTO
rec
FROM
foo
WHERE
foo_date = pfoo_date
and foovalue = pfoovalue
and footext is null
For Update;
else
SELECT
*
INTO
rec
FROM
foo
WHERE
foo_date = pfoo_date
and foovalue = pfoovalue
and footext = pfootext
For Update;
end if;
RETURN NEXT rec;
return;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION public.get_nextfoo(pfoo_date "timestamp", pfoovalue int4,
pfootext bpchar) OWNER TO fro;
insert into foo(foo_date,foovalue,footext) values('2006-08-15',1,'bar');
insert into foo(foo_date,foovalue,footext) values('2006-08-14',1,'bar');
insert into foo(foo_date,foovalue,footext) values('2006-08-15',2,'bar');
insert into foo(foo_date,foovalue,footext) values('2006-08-14',2,'bar');
insert into foo(foo_date,foovalue) values('2006-08-15',1);
insert into foo(foo_date,foovalue) values('2006-08-14',1);
insert into foo(foo_date,foovalue) values('2006-08-15',2);
insert into foo(foo_date,foovalue) values('2006-08-14',2);
Thanks,
Curtis
The material contained in this email may be confidential, privileged or
copyrighted. If you are not the intended recipient, use, disclosure or copying
of this information is prohibited. If you have received this document in error,
please advise the sender and delete the document. Neither OneSteel nor the
sender accept responsibility for any viruses contained in this email or any
attachments.
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] CONSTRAINT REFERENCE betwen array to single
Hello I have a table with an array of smallint that I want reference to a column of smallint, see below: /database=# CREATE TABLE id_names ( id smallint CONSTRAINT the_id PRIMARY KEY NOT NULL, database(# name text CONSTRAINT the_name UNIQUE ) WITH OIDS; NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "the_id" for table "id_names" NOTICE: CREATE TABLE / UNIQUE will create implicit index "the_name" for table "id_names" CREATE TABLE database=# CREATE TABLE constrain_id ( id smallint[] CONSTRAINT the_id_constr REFERENCES id_names (id) ) WITH OIDS; ERROR: foreign key constraint "the_id_constr" cannot be implemented DETAIL: Key columns "id" and "id" are of incompatible types: smallint[] and smallint./ I mean, each of the possibles values in constrain_id.id[i] must be in id_names.id . There are any way to do it with CONSTRAINT ??? Thank you very much, Gustavo ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Changing the data directory Ubuntu
On 15 Aug 2006 11:19:39 -0700 [EMAIL PROTECTED] wrote: > Hello, > > I have done a good deal of investigation and cannot seem to find a > straight answer. Is there way to change the default data directory? > I am using Ubuntu Dapper LTS. I have a seperate hard drive (and > partition) that I want to keep my data on in case of a problem with > the OS. > > Any help would be appreciated. > > Shaun Shaun, Normally the "default data directory" is set when starting PostgreSQL with the -D switch. Sometimes it is controled by the PGDATA environmental variable. On my Ubuntu Dapper system PostgreSQL (which was build from source, not the .deb) this is set from SysV startup script located at /etc/init.d/postgresql. To "change" the default directory run initdb -D "New Directory" then change the data directory in the SysV init script. Stop and restart PostgreSQL. John Purser ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Changing the data directory Ubuntu
On Aug 15, 2006, at 1:19 PM, [EMAIL PROTECTED] wrote: Hello, I have done a good deal of investigation and cannot seem to find a straight answer. Is there way to change the default data directory? I am using Ubuntu Dapper LTS. I have a seperate hard drive (and partition) that I want to keep my data on in case of a problem with the OS. Any help would be appreciated. Shaun Unless Ubuntu is doing anything funny, you should be able to set data_directory in postgresql.conf: http://www.postgresql.org/docs/8.1/static/runtime-config-file- locations.html -- Thomas F. O'Connell Sitening, LLC http://www.sitening.com/ 3004B Poston Avenue Nashville, TN 37203-1314 615-469-5150 x802 615-469-5151 (fax) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Changing the data directory Ubuntu
Hello, I have done a good deal of investigation and cannot seem to find a straight answer. Is there way to change the default data directory? I am using Ubuntu Dapper LTS. I have a seperate hard drive (and partition) that I want to keep my data on in case of a problem with the OS. Any help would be appreciated. Shaun ---(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] trigger speed
I have a trigger that updates a count table, based on status. The count table looks like this: key status count a1 300 a2 400 b1 100 b2 200 The problem is that for large updates when I do "UPDATE table SET status = 1 WHERE status = 2 and key = 'a';" the row level trigger fires for each row updated, decrementing the a 2 row and incrmenting the a 1 row. For large updates this really slows things down. Question #1: how do I speed this up? I need a way to run a trigger on all rows at once. Q #2: how do satement level triggers work? The examples in the pg docs only show row level triggers. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Partial indexes Vs standard indexes : Insert performance
Gregory Stark <[EMAIL PROTECTED]> writes: > But if you're just looking up a single record I wouldn't expect it to be much > faster to look it up in the smaller partial index than in the larger index. > Indexes find records in log(n) time and log() grows awfully slowly. Yeah. Given the proportions mentioned in the original message, I think one index on the whole table and one on just the ICMP records is probably the best solution. A partial index covering most of a table is not going to win enough to justify its maintenance overhead. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL]
Jeff Davis <[EMAIL PROTECTED]> writes: > What you want to do is pass each "perm_field" as a parameter. I think the OP is hoping for some sort of magic action-at-a-distance behavior whereby his function can access stuff that wasn't passed to it. Doesn't exist though ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] REFERENCE problem with parent_table
> > it looks like the syntax here is a little off from what is defined by: > > http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html > > > > CREATE TABLE like_id_1 ( idsmallint like id_names, > > name text like id_name ) with oids ; > > > Are you sure, I think that you are wrong. Operator "LIKE" is like a > operator for a "table_constraint", I mean, is wrote in a new line, like > a new column, and isn't property or part of any column. More over, in > the documentation is wrote: "/The LIKE clause specifies a table from > which the new table automatically copies all column names, their data > types, and their not-null constraints./", but is like the operator > "REFERENCE" see the tables "like_id_1" empty (see below) or maybe is a > bug in posgresql. > /database=# insert into like_id_1 values (1,'hello'); > INSERT 157581 1 > database=# select * from like_id_1; > id | name > + > 1 | hello > (1 row) > database=# CREATE TABLE ref_1 ( id smallint CONSTRAINT the_id > REFERENCES like_id_1 (id) ); > ERROR: there is no unique constraint matching given keys for > referenced table "like_id_1"/ You are correct, I miss-spoke. :o) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] REFERENCE problem with parent_table
On Tue, 15 Aug 2006, gustavo halperin wrote: > Hello > > I need many tables of type "id" and "name", see below: > / CREATE TABLE id_names ( > idsmallintCONSTRAINT the_id PRIMARY KEY NOT NULL, > nametextCONSTRAINT the_name UNIQUE > ) WITH OIDS;/ > therefore I created these tables with the "LIKE" operator, see below: > /CREATE TABLE like_id_1( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; > CREATE TABLE like_id_2 ( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; > CREATE TABLE like_id_3 / > Next I can't create a table with some column reference to any of the > last two tables, see below: > /database=# CREATE TABLE ref_1 ( id_1 smallint CONSTRAINT the_id_1 > REFERENCES like_id_1 (id) ); > ERROR: there is no unique constraint matching given keys for > referenced table "like_id_1"/ > Obviously if I use "id_names" instead of "like_id_1" every think is > fine but my idea is not create thousands of almost same tables with the > table name's like the only one difference. Then I thought to use the > operator "LIKE", but you see, there are a problem. Any Idea about what > must I do ?? The LIKE clause doesn't copy the UNIQUE/PRIMARY KEY constraints from id_names. You'll probably need to add the constraint information to the other tables. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] REFERENCE problem with parent_table
Richard Broersma Jr wrote: I need many tables of type "id" and "name", see below: / CREATE TABLE id_names ( idsmallintCONSTRAINT the_id PRIMARY KEY NOT NULL, nametextCONSTRAINT the_name UNIQUE ) WITH OIDS;/ therefore I created these tables with the "LIKE" operator, see below: /CREATE TABLE like_id_1( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; CREATE TABLE like_id_2 ( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; it looks like the syntax here is a little off from what is defined by: http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html CREATE TABLE like_id_1 ( idsmallint like id_names, name text like id_name ) with oids ; Are you sure, I think that you are wrong. Operator "LIKE" is like a operator for a "table_constraint", I mean, is wrote in a new line, like a new column, and isn't property or part of any column. More over, in the documentation is wrote: "/The LIKE clause specifies a table from which the new table automatically copies all column names, their data types, and their not-null constraints./", but is like the operator "REFERENCE" see the tables "like_id_1" empty (see below) or maybe is a bug in posgresql. /database=# insert into like_id_1 values (1,'hello'); INSERT 157581 1 database=# select * from like_id_1; id | name + 1 | hello (1 row) database=# CREATE TABLE ref_1 ( id smallint CONSTRAINT the_id REFERENCES like_id_1 (id) ); ERROR: there is no unique constraint matching given keys for referenced table "like_id_1"/ Thank you any way, Gustavo ---(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] REFERENCE problem with parent_table
> I need many tables of type "id" and "name", see below: > / CREATE TABLE id_names ( > idsmallintCONSTRAINT the_id PRIMARY KEY NOT NULL, > nametextCONSTRAINT the_name UNIQUE > ) WITH OIDS;/ > therefore I created these tables with the "LIKE" operator, see below: > /CREATE TABLE like_id_1( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; > CREATE TABLE like_id_2 ( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; it looks like the syntax here is a little off from what is defined by: http://www.postgresql.org/docs/8.1/interactive/sql-createtable.html CREATE TABLE like_id_1 ( idsmallint like id_names, name text like id_name ) with oids ; Regards, Richard Broersma jr. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] REFERENCE problem with parent_table
Hello I need many tables of type "id" and "name", see below: CREATE TABLE id_names ( id smallint CONSTRAINT the_id PRIMARY KEY NOT NULL, name text CONSTRAINT the_name UNIQUE ) WITH OIDS; therefore I created these tables with the "LIKE" operator, see below: CREATE TABLE like_id_1( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; CREATE TABLE like_id_2 ( LIKE id_names INCLUDING DEFAULTS ) WITH OIDS; CREATE TABLE like_id_3 Next I can't create a table with some column reference to any of the last two tables, see below: database=# CREATE TABLE ref_1 ( id_1 smallint CONSTRAINT the_id_1 REFERENCES like_id_1 (id) ); ERROR: there is no unique constraint matching given keys for referenced table "like_id_1" Obviously if I use "id_names" instead of "like_id_1" every think is fine but my idea is not create thousands of almost same tables with the table name's like the only one difference. Then I thought to use the operator "LIKE", but you see, there are a problem. Any Idea about what must I do ?? Thank you, Gustavo
[GENERAL] plpgsql dynamic queries and optional arguments
I have a table that I would like to be
able to retrieve information out of based on a combination of multiple columns
and I would like to be able to do this through a plpgsql stored procedure.
Right now I have multiple stored procedures that I am calling based on the
values parameter values I pass them and I am using static sql. The problem with
this is it doesn’t scale as well as I would like it to because when I add
another column of information to the table that needs to be used for retrieval it
adds another level of combinations.
Also, when dealing with null values with
static sql I use the same exact sql statement except for the where clause
containing the “column1 is null” versus “column1 =
passedvalue”. Anyways, I have made a simple example procedure and table;
any help would be greatly appreciated basically I would like to use dynamic sql
instead of static but I have unsuccessfully been able to retrieve the results
of a dynamic sql statement in a pgplsql procedure. Here is the example table
and stored procedure.
CREATE TABLE public.foo
(
fooid int4 NOT
NULL DEFAULT nextval('foo_fooid_seq'::regclass),
foo_date timestamp
NOT NULL,
footypeid int4 NOT
NULL,
footext varchar,
CONSTRAINT
pk_fooid PRIMARY KEY (fooid)
)
WITHOUT OIDS;
ALTER TABLE public.foo
OWNER TO fro;
CREATE OR REPLACE
FUNCTION public.get_nextfoo(pfoo_date "timestamp", pfoovalue int4,
pfootext bpchar)
RETURNS SETOF
public.foo AS
$BODY$DECLARE
rec
foo%ROWTYPE;
BEGIN
if
pfootext is null then
SELECT
*
INTO
rec
FROM
foo
WHERE
foo_date = pfoo_date
and foovalue = pfoovalue
and footext is null
For Update;
else
SELECT
*
INTO
rec
FROM
foo
WHERE
foo_date = pfoo_date
and foovalue = pfoovalue
and footext = pfootext
For Update;
end
if;
RETURN
NEXT rec;
return;
END;
$BODY$
LANGUAGE 'plpgsql'
VOLATILE;
ALTER FUNCTION
public.get_nextfoo(pfoo_date "timestamp", pfoovalue int4, pfootext
bpchar) OWNER TO fro;
insert into
foo(foo_date,foovalue,footext) values('2006-08-15',1,'bar');
insert into
foo(foo_date,foovalue,footext) values('2006-08-14',1,'bar');
insert into
foo(foo_date,foovalue,footext) values('2006-08-15',2,'bar');
insert into
foo(foo_date,foovalue,footext) values('2006-08-14',2,'bar');
insert into
foo(foo_date,foovalue) values('2006-08-15',1);
insert into
foo(foo_date,foovalue) values('2006-08-14',1);
insert into
foo(foo_date,foovalue) values('2006-08-15',2);
insert into
foo(foo_date,foovalue) values('2006-08-14',2);
Thanks,
Curtis
Re: [GENERAL]
On Tue, 2006-08-15 at 18:46 +0200, Max wrote: > Thx. > But I know how to write procedure and function, but my problem is to > know how to access the current row fields during a SELECT inside a > function: > > So, in a function, can I write : > > /* ... */ permission (/* ... */) > /* ... */ > IF (ROW.perm_field1 = 1) > statement > IF (some_operation(ROW.perm_field2)) > statement > /* ... */ > RETURN TRUE or FALSE; > /* ... */ > What you want to do is pass each "perm_field" as a parameter. So, you'd do something like: CREATE OR REPLACE FUNCTION permission(perm_field1 int, perm_field2 int, perm_field3 int) RETURNS BOOLEAN LANGUAGE plpgsql STABLE AS $$ BEGIN IF perm_field1 = 2 THEN RETURN FALSE; ELSIF perm_field2 = perm_field3 THEN RETURN TRUE; ELSE RETURN FALSE; END IF; END; $$; And then: SELECT * FROM tablename WHERE permission (perm_field1,perm_field2,perm_field3); Hope this helps, Jeff Davis ---(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] Unable to Start PostgreSQL 8.1.4 on Windows XP
Hi Thomas : --- Thomas Kellerer <[EMAIL PROTECTED]> wrote: > Ludwig Isaac Lim wrote on 15.08.2006 18:05: > > Searching the web using google gives me the following > > information about error 1063: > > Error code 1063: > ERROR_FAILED_SERVICE_CONTROLLER_CONNECT - > I had a similar issue several weeks ago. In my case the > UMTS connection software > killed my Postgres installation. Did you change anything > with your network > installation? > > In my case either de-installing the UMTS software or > applying a tool called > LSPFix would fix the problem: > http://www.cexx.org/lspfix.htm > > Hope this helps > Thomas > Thanks for the quick reply. Actually, after running the newly installed anti-spyware program a week ago I was unable to access my internet. I'm not sure if the anti-spyware program caused it though. During that period, my computer was infected with spware,adware,virus. It was only until after running lspfix that I was able to access the internet. After reading your email, I tried running lspfix again but it seems that this time, lspfix didn't detect any problem in my network protocol drivers. Thanks for the idea though, it was a good one. From what you had said, I have a hunch that the problem lies with the network protocol driver. ludwig lim __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] wal files on temporary tables
Tom thanks for to respond quickly, see below > marcelo Cortez <[EMAIL PROTECTED]> writes: > > Then i've created "normal" tables but the wal > file > > generated by this tables grows and grows. > > The WAL files shouldn't grow indefinitely unless > you've got some > fairly serious problem that is preventing > checkpoints from occurring. > Look in the server log file to see if there are any > error messages > about that. The select as sentence is used for generated "temporary" or cached tables. Some tables are very big and generate great quantity of data, for that reason the wal for this tables grows. This information is useless for backup and is useless to keep it. I explore memcached option or pgmemcache proyect. http://pgfoundry.org/projects/pgmemcache/ any sugestion are welcomed. best regads, MDC > > regards, tom lane > __ Preguntá. Respondé. Descubrí. Todo lo que querías saber, y lo que ni imaginabas, está en Yahoo! Respuestas (Beta). ¡Probalo ya! http://www.yahoo.com.ar/respuestas ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Partial indexes Vs standard indexes : Insert performance
MaXX <[EMAIL PROTECTED]> writes: > In my understanding, a partial index is only touched when a matching row is > inserted/updated/deleted (index constraint is true), so if I create a partial > index for each protocol, I will slow down my machine as if I had created a > single "normal" index, but it will find rows faster (the distribution is not > uniform)... > > Is this correct? Everything up to the "find rows faster" is pretty much true. "find rows faster" depends on exactly how you define your indexes, what your queries look like, and what the distribution of both the queries and the data look like. Where it really helps is when you're processing a whole bunch of records and using the partial index expression in addition the key column effectively lets you combine two constraints on your query. To get the same effect without the partial index you would either need a compound key which would take a lot more space and cause more i/o or you would need two separate indexes that postgres would combine with a bitmap index scan but that wouldn't be as effective. So for example if there are a million packets to a given host but only 100k that were TCP then a partial index on would let you scan only the 100k instead of having to scan the million and look at each one to discard it. And it would let you do that without having to create a much larger index on or combine two indexes one on and one on either of which would be much slower and take more space. But if you're just looking up a single record I wouldn't expect it to be much faster to look it up in the smaller partial index than in the larger index. Indexes find records in log(n) time and log() grows awfully slowly. At best you're basically skipping a single tree level in favour of earlier query planning which is probably not going to be noticeable. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Connection string
Its doneservice iptables stop did the trickfirewall was running on my DB serverforgot to stop itThanks~HarpreetOn 8/15/06, Michael Fuhr
<[EMAIL PROTECTED]> wrote:On Sun, Aug 13, 2006 at 10:30:24AM +0200, Michael Meskes wrote:
> On Fri, Aug 11, 2006 at 04:40:36PM -0600, Michael Fuhr wrote:> > Will you take care of it or should I submit a patch? I've noticed>> I you have the time to write the patch I woul dappreciate it.
I'll submit a patch. However, in the case of string literals notworking, is that a documentation bug or a code bug? Are theysupposed to work?> > a few other discrepancies between the documentation and actual
> > behavior, like examples with "VARCHAR val;" that the preprocessor> > rejects with "ERROR: pointer to varchar are not implemented.">> Do you have an example? This surely looks like a bug.
See several code examples in the last half of the "Using HostVariables" documentation:http://www.postgresql.org/docs/8.1/interactive/ecpg-variables.html
Here's a complete example with code pasted from the documentation:% cat foo.pgcint main(void){EXEC SQL BEGIN DECLARE SECTION;int v1;VARCHAR v2;EXEC SQL END DECLARE SECTION;
return 0;}% ecpg foo.pgcfoo.pgc:5: ERROR: pointer to varchar are not implementedIs that a documentation bug or a code bug?--Michael Fuhr---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL]
Thx. But I know how to write procedure and function, but my problem is to know how to access the current row fields during a SELECT inside a function: So, in a function, can I write : /* ... */ permission (/* ... */) /* ... */ IF (ROW.perm_field1 = 1) statement IF (some_operation(ROW.perm_field2)) statement /* ... */ RETURN TRUE or FALSE; /* ... */ Or am I obligated to pass to my function the tablename and the id field to execute a second query, then retrieving the row fields and then computing permissions and then returning true or false ? Thx for Help Max Le 15 août 06 à 18:27, Jeff Davis a écrit : On Tue, 2006-08-15 at 18:05 +0200, Max wrote: Do you know if it is possible to write such things : SELECT * FROM tablename WHERE permission(some parameters) == TRUE; with the function 'permission' returning TRUE or FALSE after a check on different perm_fields of the current row. Yes, you can do that. The best place to start is: http://www.postgresql.org/docs/8.1/static/plpgsql.html You want to create a pretty basic function that returns "boolean" and takes a few parameters. Note that SQL does not have the C-style "==" equality test. In SQL just write "= true". Better yet, since your function returns boolean just do "WHERE permission(...)" with "..." replaced by your parameters. If you have trouble after trying out some examples at that link, post back to the list with what you tried. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Unable to Start PostgreSQL 8.1.4 on Windows XP
Ludwig Isaac Lim wrote on 15.08.2006 18:05: Searching the web using google gives me the following information about error 1063: Error code 1063: ERROR_FAILED_SERVICE_CONTROLLER_CONNECT - The service process could not connect to the service controller. (from http://user.tninet.se/~tdf275m/wincode2.htm) I had a similar issue several weeks ago. In my case the UMTS connection software killed my Postgres installation. Did you change anything with your network installation? In my case either de-installing the UMTS software or applying a tool called LSPFix would fix the problem: http://www.cexx.org/lspfix.htm Hope this helps Thomas ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL]
On Tue, 2006-08-15 at 18:05 +0200, Max wrote: > Do you know if it is possible to write such things : > > SELECT * FROM tablename WHERE permission(some parameters) == TRUE; > > with the function 'permission' returning TRUE or FALSE after a check > on different perm_fields of the current row. Yes, you can do that. The best place to start is: http://www.postgresql.org/docs/8.1/static/plpgsql.html You want to create a pretty basic function that returns "boolean" and takes a few parameters. Note that SQL does not have the C-style "==" equality test. In SQL just write "= true". Better yet, since your function returns boolean just do "WHERE permission(...)" with "..." replaced by your parameters. If you have trouble after trying out some examples at that link, post back to the list with what you tried. Regards, Jeff Davis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Partial indexes Vs standard indexes : Insert
On Tue, 2006-08-15 at 13:13 +0200, MaXX wrote: > Hi, > > I just want to verify if I'm understanding this correctly: > > I have a table in which I store log from my firewall. > For the protocol column (3 distinct values: TCP ~82%, UDP ~17%, ICMP > ~1%, the table contains 1.7M rows), I use a partial index to find ICMP > packets faster. > > In my understanding, a partial index is only touched when a matching row > is inserted/updated/deleted (index constraint is true), so if I create a > partial index for each protocol, I will slow down my machine as if I had > created a single "normal" index, but it will find rows faster (the > distribution is not uniform)... > > Is this correct? That should work. Keep in mind that the main idea of an index is to reduce the number of pages that have to be fetched from disk. If the record size is small, you may have at least one ICMP packet on 50% (or more) of the disk pages even if ICMP packets only make up 1% of the total records. Even if they aren't inserted randomly, updates/deletes may randomize the distribution somewhat. If you have an ICMP packet on every other page, you might not be impressed with the performance versus a sequential scan. However, it could be a big win if you have other WHERE conditions aside from just the packet type. The planner tries to take all of these things into consideration to some degree. The best test is to try EXPLAIN or EXPLAIN ANALYZE to see what plan it makes. Also, try forcing different types of plans to see if the planner is making the right choice. Regards, Jeff Davis ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Connection string
in my previous mail both the ip addressed should be read as 192.168.0.123thanksharpreetOn 8/15/06, Harpreet Dhaliwal < [EMAIL PROTECTED]> wrote:Hi Micheal,sudde2nly a problem has cropped up in my connection. Its kind of strange.ECPGdegug(1, stderr) says[9852]: connect: cold not open database dbxyz on 192.168.0.123 port 5432 for user jsb in line 16 could not connect to server: No route to host Is the server running on host " 192.168.0.110" and accepting TCP/IP connections on port 5432? My server is very much running. I also made sure that TCP/IP connection is being accepted on port 5432 using nmap -sS localhost in my database server.Don't know whats wrong nowAlso, just an observation (don't know if that is the cause or not) In my clinet machine while compiling my test.c file usinggcc -o test test.c -lecpg -L/usr/lib/pgsqlthere's not pgsql directory in /usr/lib.Where exactly do i have to do this linking thing and to what I have to link it.Thanks ~Harpreet On 8/13/06, Michael Fuhr < [EMAIL PROTECTED]> wrote: On Sun, Aug 13, 2006 at 07:07:11PM -0400, Harpreet Dhaliwal wrote:> Problem was with pg_hba.conf file>> ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for> localhost only and not for other ip addresses. >> I had to change the configuration for IPV4 local connections>> It should have been something like>> hostall all 192.168.0.0/24 trust > instead of> host all all 127.0.0.1/32 trust (which is meant for localhost> only) >> I think i got it...right?You might want to add 192.168.0.0/24 on another line rather thanreplacing 127.0.0.1. And allowing "trust" connections is bad security practice because anybody on one of the allowed IP addresses could connect as any user without being challenged for a password.Consider using a stronger authentication method and modifying theclient code accordingly. http://www.postgresql.org/docs/8.1/interactive/auth-methods.html--Michael Fuhr
[GENERAL] Unable to Start PostgreSQL 8.1.4 on Windows XP
Hi: I installed PostgreSQL 8.1.0 on my computer running on Windows XP Service Pack 2 last April. It works fine (I don't use the database everyday though). Today, after being unable to connect to the server, I realized there must be something wrong. Looking at the logs I found out that there were no more entries after August 8. I surmise that after August 8, the PostgreSQL service is not starting anymore. I tried to start the service manually and through the command prompt, but still it didn't work. After reading the FAQ, I did the following: a) Uninstall a anti-spyware software (which was installed around August 8). b) Upgrade the server to PostgreSQL 8.1.4 c) Reboot the server c) Temporarily disable the antivirus (Rising Antirus), I did not uninstall the Antivirus because prior to August the antivirus and the database server were running together (ie. the antivirus didn't affect PostgreSQL). After taking these measures, the service is still unable to start on its own . I tried to start the service through the command line by typing the following command: C:\Program Files\PostgreSQL\8.1\bin>"C:\Program Files\PostgreSQL\8.1\bin\pg_ctl. exe" runservice -N "pgsql-8.1" -D "C:\Program Files\PostgreSQL\8.1\data\" It gives me the following error message: pg_ctl: could not start service "pgsql-8.1": error code 1063 Searching the web using google gives me the following information about error 1063: Error code 1063: ERROR_FAILED_SERVICE_CONTROLLER_CONNECT - The service process could not connect to the service controller. (from http://user.tninet.se/~tdf275m/wincode2.htm) Here are I believe the last entries in the logfile: 2006-08-08 08:14:02 LOG: database system was shut down at 2006-08-07 22:50:56 2006-08-08 08:14:02 LOG: checkpoint record is at 0/4AEF40 2006-08-08 08:14:02 LOG: redo record is at 0/4AEF40; undo record is at 0/0; shu tdown TRUE 2006-08-08 08:14:02 LOG: next transaction ID: 40061; next OID: 16447 2006-08-08 08:14:02 LOG: next MultiXactId: 1; next MultiXactOffset: 0 2006-08-08 08:14:03 LOG: database system is ready 2006-08-08 08:14:05 LOG: transaction ID wrap limit is 2147484148, limited by da tabase "postgres" 2006-08-08 09:43:03 LOG: received fast shutdown request 2006-08-08 09:43:07 LOG: shutting down 2006-08-08 09:43:07 LOG: database system is shut down 2006-08-08 09:43:09 LOG: logger shutting down Hope this helps. Thanks in advance, ludwig lim __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Connection string
Hi Micheal,sudde2nly a problem has cropped up in my connection.Its kind of strange.ECPGdegug(1, stderr) says[9852]: connect: cold not open database dbxyz on 192.168.0.123 port 5432 for user jsb in line 16 could not connect to server: No route to host Is the server running on host "192.168.0.110" and accepting TCP/IP connections on port 5432? My server is very much running. I also made sure that TCP/IP connection is being accepted on port 5432 using nmap -sS localhost in my database server.Don't know whats wrong nowAlso, just an observation (don't know if that is the cause or not) In my clinet machine while compiling my test.c file usinggcc -o test test.c -lecpg -L/usr/lib/pgsqlthere's not pgsql directory in /usr/lib.Where exactly do i have to do this linking thing and to what I have to link it.Thanks~Harpreet On 8/13/06, Michael Fuhr <[EMAIL PROTECTED]> wrote: On Sun, Aug 13, 2006 at 07:07:11PM -0400, Harpreet Dhaliwal wrote:> Problem was with pg_hba.conf file>> ECPGdebug(1, stderr); showed that in pg_hba.conf there was setting for> localhost only and not for other ip addresses. >> I had to change the configuration for IPV4 local connections>> It should have been something like>> hostall all 192.168.0.0/24 trust > instead of> host all all 127.0.0.1/32 trust (which is meant for localhost> only)>> I think i got it...right?You might want to add 192.168.0.0/24 on another line rather thanreplacing 127.0.0.1. And allowing "trust" connections is badsecurity practice because anybody on one of the allowed IP addresses could connect as any user without being challenged for a password.Consider using a stronger authentication method and modifying theclient code accordingly. http://www.postgresql.org/docs/8.1/interactive/auth-methods.html--Michael Fuhr
Re: [GENERAL] Using SETOF functions in SQL
On Tue, 2006-08-15 at 12:38 +0100, Pavel Velikhov wrote: > SELECT t1.node, t2.node, path FROM t1,t2, connections(t1.id, t2.id) as > path > > I get the notorious: ERROR: subquery in FROM may not refer to other > relations of same query level > The FROM list must be a list of relations, but in that situation, connections() creates a different relation for each relation in the join of t1 and t2. You certainly don't want to join a variable number of relations together (nor is that allowed). You could make connections() return the entire set of all connections and join based on t1.id and t2.id. Another way to do it would be to do something like "SELECT t1.node, t2.node, get_path(t1.node,t2.node) from t1, t2 where get_path (t1.node,t2.node) is not null". Then just make get_path() return null if the nodes aren't connected. Regards, Jeff Davis ---(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
[GENERAL]
Hello all. I'm working on a kind of permissions management and I would like to add restrictions on SELECT statements depending on certain fields of a row. tablename : id, perm_field1, .. .. perm_field2, data_field1, data_field2, ... Do you know if it is possible to write such things : SELECT * FROM tablename WHERE permission(some parameters) == TRUE; with the function 'permission' returning TRUE or FALSE after a check on different perm_fields of the current row. My problem is that I don't really know how to access to the fields of the current row with an external function. How could I write this ? Thx for help. Max ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Using SETOF functions in SQL
Hi! Is it possible to use a function that returns SETOF and doesn't take constant parameters?I have a function 'connections(id1 bigint, id2 bigint) returns setof text' that I use to list all paths from id1 to id2. However, I haven't figured out a way to call the function with non-constant functions. For example, if I try to write:SELECT t1.node, t2.node, path FROM t1,t2, connections(t1.id, t2.id) as pathI get the notorious: ERROR: subquery in FROM may not refer to other relations of same query levelIs there a way to reformulate the query in SQL, or I am stuck with PgSQL now?Thanks!Pavel VelikhovISP RAS
Re: [GENERAL] Excluding a table from pg_dump
Greg Sabino Mullane wrote: The database contains several schemas and excluding "comment_archive" by moving it to different schema doesn't sound very convenient. pg_dump doesn't have an option to dump multiple schemas at once. Are there any working "-X" patches for pg_dump or does anyone have other possible solutions? The next version of Postgres (8.2) will have the ability to do everything you want. It's active now in cvs, if you want to try it out. You can exclude one or more tables with the -T flag, and can include or exclude schemas with the -n and -N resepectively. Any of the four flags can be used multiple times, and they all accept POSIX-style regular expressions as well. - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation That's excellent news! I've needed this feature for a while now too. Regards Ron St.Pierre ---(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] Connection string
On Tue, Aug 15, 2006 at 05:59:23AM -0600, Michael Fuhr wrote: > I'll submit a patch. However, in the case of string literals not > working, is that a documentation bug or a code bug? Are they > supposed to work? You shoudl be able to use a string constant or a char * variable as database name. There are a lot of test cases for connect available under ecpg/test/connect now. If string literals do not work we have fix it and add it to the test suite. > > > a few other discrepancies between the documentation and actual > > > behavior, like examples with "VARCHAR val;" that the preprocessor > > > rejects with "ERROR: pointer to varchar are not implemented." > > > > Do you have an example? This surely looks like a bug. > > See several code examples in the last half of the "Using Host > Variables" documentation: Ah, I see. It lacks the size information. Yes, this is a documentation bug. varchar is translated to a struct with an char array whose size needs to be given. I think it never worked without it. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Excluding a table from pg_dump
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > The database contains several schemas and excluding "comment_archive" by > moving it to different schema doesn't sound very convenient. pg_dump > doesn't have an option to dump multiple schemas at once. > > Are there any working "-X" patches for pg_dump or does anyone have other > possible solutions? The next version of Postgres (8.2) will have the ability to do everything you want. It's active now in cvs, if you want to try it out. You can exclude one or more tables with the -T flag, and can include or exclude schemas with the -n and -N resepectively. Any of the four flags can be used multiple times, and they all accept POSIX-style regular expressions as well. - -- Greg Sabino Mullane [EMAIL PROTECTED] End Point Corporation PGP Key: 0x14964AC8 200608150821 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFE4bxzvJuQZxSWSsgRAgYPAJ9qa/jE5oHY/DMOGNfuHsoVgiwf4gCgjnHW FqZF5l51h4j/ul+dK7M90DE= =VSi/ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Connection string
On Sun, Aug 13, 2006 at 10:30:24AM +0200, Michael Meskes wrote:
> On Fri, Aug 11, 2006 at 04:40:36PM -0600, Michael Fuhr wrote:
> > Will you take care of it or should I submit a patch? I've noticed
>
> I you have the time to write the patch I woul dappreciate it.
I'll submit a patch. However, in the case of string literals not
working, is that a documentation bug or a code bug? Are they
supposed to work?
> > a few other discrepancies between the documentation and actual
> > behavior, like examples with "VARCHAR val;" that the preprocessor
> > rejects with "ERROR: pointer to varchar are not implemented."
>
> Do you have an example? This surely looks like a bug.
See several code examples in the last half of the "Using Host
Variables" documentation:
http://www.postgresql.org/docs/8.1/interactive/ecpg-variables.html
Here's a complete example with code pasted from the documentation:
% cat foo.pgc
int main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
int v1;
VARCHAR v2;
EXEC SQL END DECLARE SECTION;
return 0;
}
% ecpg foo.pgc
foo.pgc:5: ERROR: pointer to varchar are not implemented
Is that a documentation bug or a code bug?
--
Michael Fuhr
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] Partial indexes Vs standard indexes : Insert performance
Hi, I just want to verify if I'm understanding this correctly: I have a table in which I store log from my firewall. For the protocol column (3 distinct values: TCP ~82%, UDP ~17%, ICMP ~1%, the table contains 1.7M rows), I use a partial index to find ICMP packets faster. In my understanding, a partial index is only touched when a matching row is inserted/updated/deleted (index constraint is true), so if I create a partial index for each protocol, I will slow down my machine as if I had created a single "normal" index, but it will find rows faster (the distribution is not uniform)... Is this correct? Thanks a lot, -- MaXX ---(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] Explain returns extremely high cost
On Tue, Aug 15, 2006 at 10:14:49AM +0300, Andrii Vasyliev wrote: > Hi! > > I have a database just recreated from scratch with SQL script. > (My PostgreSQL version is 8.1.3). > All of the tables are pretty empty, but that's what I've got with explain: > > EXPLAIN SELECT * FROM account; > QUERY PLAN > > Seq Scan on account (cost=1.00..10001.05 rows=5 width=41) Looks like someone did an "enable_seqscan=no" somewhere, do a "show all" and check the planner variables... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
[GENERAL] Excluding a table from pg_dump
Hi, I think there has been some talk about this missing feature since 2001, but anyway.. I have a database which contains about 500M rows and the nightly dump takes about six hours. Size of the compressed dump is ~30GB. The biggest tables are: relname| reltuples -+- comment_archive | 2.45995e+08 comment | 1.68875e+08 Comments are messages written by users and they are "partitioned" into two tables. New messages are always written to "comment" and once a month the oldest messages are moved to "comment_archive". For performance reasons neither of these tables have foreign keys. Contents of "comment_archive" are quite static. It's frequently read but rarely written. Backup each night is quite useless and it steals time from other essential maintenance tasks. The database contains several schemas and excluding "comment_archive" by moving it to different schema doesn't sound very convenient. pg_dump doesn't have an option to dump multiple schemas at once. Are there any working "-X" patches for pg_dump or does anyone have other possible solutions? |\__/| ( oo )Kari Lavikka - [EMAIL PROTECTED] - (050) 380 3808 __ooO( )Ooo___ _ ___ _ _ _ __ _ _ "" ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Explain returns extremely high cost
Hi! I have a database just recreated from scratch with SQL script. (My PostgreSQL version is 8.1.3). All of the tables are pretty empty, but that's what I've got with explain: EXPLAIN SELECT * FROM account; QUERY PLAN Seq Scan on account (cost=1.00..10001.05 rows=5 width=41) This is what I have in the table: SELECT * FROM account; obj_id | login | password | client_id | service_id | type_id | state_id -+-+-+---++-+-- 1000513 | root| _system | 1000471 |1000486 | 1000113 | 1000209 1000548 | root| _system | 1000472 |1000536 | 1000113 | 1000209 1000550 | root| _system | 1000475 |1000534 | 1000113 | 1000209 1000552 | sol | _sol| 1000472 |1000536 | 1000113 | 1000209 1000554 | seregik | _seregik| 1000475 |1000534 | 1000113 | 1000209 (5 rows) Time: 1.346 ms For other tables I have more or less the same high costs. I've done VACUUM ANALYZE. This is what I have in pg_class table: SELECT relpages,reltuples FROM pg_class WHERE relname='account'; relpages | reltuples --+--- 1 | 5 Why the costs are so high? Is it a bug? Thanks in advance for any suggestions! Regards, Andrii ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
