Re: [SQL] automatic update or insert

[PFC]

In a system of mine i need to insert records into table [tbStat], and
if the records exist i need to update them instead and increase a
column [cQuantity] for every update.

I.e. the first insert sets cQuantity to 1, and for every other run
cQuantity is increased.

Currently i have implemented this as a stored procedure in the plpgsql
language. This means that in my stored procedure i first do a select to
find out if the row exists or not, then i do a insert or update
depending if the row existed.


There are two ways you can do this :

* If you will have more updates than inserts (ie. more items with a  
quantity >1 than 1) :


UPDATE
If the update updated no rows, then INSERT

* If you have more inserts than updates (ie. more items with quantity 1  
than >1) :


INSERT
if it fails due to violating the unique constraint, then UPDATE

	None of these involve a SELECT. The first one is very cheap if you end up  
doing more updates than inserts, because it just does the update.


	You will of course need a UNIQUE index to identify your rows, and prevent  
insertion of duplicates. I suppose you have this already.
	There is a subtility in the second form : the INSERT will fail on  
duplicate key, so you have to either rollback the transaction if you send  
the queries raw from your app, or catch the exception in your plpgsql  
function.
	Also a race condition might exist if someone deletes a row in-between, or  
the first procedure is executed twice at the same time by different  
threads. Be prepared to retry your transaction.


Something like the ON DUPLICATE KEY UPDATE in MySQL would be nice to 
have.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] convert timezone to string ...

[Magnus Hagander]

> I know that the server knows that ADT == -0400, and AST == 
> -0300 ... is there any way of reversing that?  Basically, I 
> want to say:
> 
> SELECT timezone_str(-0400, 'not dst');
> 
> and have it return ADT ... I've got a method of doing it 
> right now, using a function, but just find it looks so messy, 
> just wondering if there is a clean way of doing it ...
> 
> Thanks ...

How would you know *which* timezone to go back to? For every hourly
offset there is a whole set of timezones that would result in it...

//Magnus

---(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: [SQL] writable joined view

[Wiebe Cazemier]

Sarah Asmaels wrote:

Hi!

I have one table referencing an object in another table through an ID,
and a view joining those tables on the ID. I want to create rules to
rewrite updates/deletes/inserts on the joined view to act on the real
tables. Can you give me some pointers? The documentation has only
examples for views depending on single tables.

Thank you,

Sarah


Is there any difference in multi or single table view? When you create a rule, 
you have access to NEW and/or OLD, which will contain all the fields your view 
has. You can then do something like this:


CREATE RULE insert_rule AS ON INSERT TO your_view DO INSTEAD (
INSERT INTO table1 (name) VALUES (NEW.name);
INSERT INTO table2 (favorite_color) VALUES (NEW.favorite_color);
);

Or is there something I'm not understanding about your request, or perhaps rules 
in general?


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] convert timezone to string ...

[Tom Lane]

"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> I know that the server knows that ADT == -0400, and AST == -0300 ...

Other way around isn't it?  Unless Canada observes a pretty strange
variety of daylight saving time ;-)

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [SQL] Merging lines with NULLs (with example data)

[Harald Fuchs]

In article <[EMAIL PROTECTED]>,
MaXX <[EMAIL PROTECTED]> writes:

> How can I "merge" this 
> gday,count_udp,count_tcp
> '2005-10-20','','2'
> '2005-10-20','3',''
> '2005-10-21','','1'
> '2005-10-21','5',''

> into that:
> gday,count_udp,count_tcp
> '2005-10-20','3','2'
> '2005-10-21','5','1'

> in a single query???

Try something like that:

  SELECT to_date (tstamp,'-MM-DD') AS gday,
 sum(CASE WHEN proto = 'UDP' THEN 1 ELSE 0 END) AS count_udp,
 sum(CASE WHEN proto = 'TCP' THEN 1 ELSE 0 END) AS count_tcp
  FROM test 
  WHERE tstamp >= now() - INTERVAL '$days DAYS'
AND dst_port = $port
  GROUP BY gday
  ORDER BY gday


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] Merging lines with NULLs (with example data)

[Daryl Richter]

Harald Fuchs wrote:

In article <[EMAIL PROTECTED]>,
MaXX <[EMAIL PROTECTED]> writes:


How can I "merge" this 
gday,count_udp,count_tcp

'2005-10-20','','2'
'2005-10-20','3',''
'2005-10-21','','1'
'2005-10-21','5',''




into that:
gday,count_udp,count_tcp
'2005-10-20','3','2'
'2005-10-21','5','1'




in a single query???



Try something like that:

  SELECT to_date (tstamp,'-MM-DD') AS gday,
 sum(CASE WHEN proto = 'UDP' THEN 1 ELSE 0 END) AS count_udp,
 sum(CASE WHEN proto = 'TCP' THEN 1 ELSE 0 END) AS count_tcp
  FROM test 
  WHERE tstamp >= now() - INTERVAL '$days DAYS'

AND dst_port = $port
  GROUP BY gday
  ORDER BY gday



Or, via a subquery:

select distinct to_date(tstamp,'-MM-DD') as gday,
( select count(id) from test t1 where proto='UDP' and 
to_date(t1.tstamp,'-MM-DD') = to_date(test.tstamp,'-MM-DD') ) as 
count_udp,
( select count(id) from test t1 where proto='TCP' and 
to_date(t1.tstamp,'-MM-DD') = to_date(test.tstamp,'-MM-DD') ) as 
count_tcp

from test
where tstamp >= (now() - interval '6 days' )
and dst_port = 2290
order by gday;

Harald's solution is better for your particular case and will almost 
certainly be faster, but subqueries are good to know how to do. :)




---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org



--
Daryl Richter
Platform Author & Director of Technology

(( Brandywine Asset Management  )
 ( "Expanding the Science of Global Investing"  )
 (  http://www.brandywine.com   ))


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] automatic update or insert

[Daryl Richter]

tobbe wrote:

Hi.

I have a little problem.

In a system of mine i need to insert records into table [tbStat], and
if the records exist i need to update them instead and increase a
column [cQuantity] for every update.

I.e. the first insert sets cQuantity to 1, and for every other run
cQuantity is increased.



Regardless of how you implemented it, this seems unwise.  You can never 
know, nor validate, that this quantity is definitely correct.  Why can't 
you just insert another row and then count them?


If this is a transient value you might be ok, but I generally wouldn't 
put it in a DB in that case anyway...



Currently i have implemented this as a stored procedure in the plpgsql
language. This means that in my stored procedure i first do a select to
find out if the row exists or not, then i do a insert or update
depending if the row existed.

Unfortunately, stored procedures seems awfully slow. And i need the
application to go faster.

One solution could be to implement the stored procedure in my program
instead. I think that this will be atleast 50% faster than my stored
procedure, so that would be ok.

However, this has made me thinking. Couldn't this be done directly in
SQL?


Brgds Robert


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


--
Daryl Richter
Platform Author & Director of Technology

(( Brandywine Asset Management  )
 ( "Expanding the Science of Global Investing"  )
 (  http://www.brandywine.com   ))


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [SQL] writable joined view

[Richard Huxton]

Sarah Asmaels wrote:

Hi!

I have one table referencing an object in another table through an ID,
and a view joining those tables on the ID. I want to create rules to
rewrite updates/deletes/inserts on the joined view to act on the real
tables. Can you give me some pointers? The documentation has only
examples for views depending on single tables.


I've attached a small example script that shows insert/update/delete on 
a "joined" view.


--
  Richard Huxton
  Archonet Ltd
-- Rules on joined tables
--	Below are two tables: contact, contact_emails
--	Email addresses with a priority of 0 are considered "default"
--	Contacts can be either personal (PNL) or business (BUS)
--
BEGIN;

CREATE TABLE contacts (
	id int4 NOT NULL UNIQUE,
	full_name  varchar(100),
	con_type   varchar(3) NOT NULL DEFAULT ('PNL') CHECK (con_type IN ('PNL','BUS')),

	PRIMARY KEY (id)
);

CREATE TABLE contact_emails (
	contact  int4 NOT NULL REFERENCES contacts,
	pri  int2 CHECK (pri >= 0),
	emailvarchar(100),

	PRIMARY KEY (contact, pri)
);


COPY contacts (id,full_name,con_type) FROM stdin;
1	Aaron Aardvark	PNL
2	Betty Bee	PNL
3	Carl Cat	PNL
4	Deputy Dawg	BUS
5	Eric Elephant	BUS
6	Fran Fish	BUS
\.

COPY contact_emails (contact,pri,email) FROM stdin;
1	0	[EMAIL PROTECTED]
1	1	[EMAIL PROTECTED]
2	0	[EMAIL PROTECTED]
3	0	[EMAIL PROTECTED]
4	0	[EMAIL PROTECTED]
4	1	[EMAIL PROTECTED]
5	0	[EMAIL PROTECTED]
6	0	[EMAIL PROTECTED]
\.
COMMIT;

-- contact_defaults
--	A view that shows the default email for each contact.
--	There are rules that allow updating of the view.
--	Note how when deleting, we ignore "pri", but when updating we make sure it is set to 0
--	Obviously, we could have handled deleting emails through a FK cascade.
--
BEGIN;

CREATE VIEW contact_defaults AS
SELECT
	c.id AS con_id,
	c.full_name,
	c.con_type,
	e.email
FROM
	contacts c,
	contact_emails e
WHERE
	c.id = e.contact
	AND e.pri = 0
;

CREATE OR REPLACE RULE con_def_del AS ON DELETE TO contact_defaults
DO INSTEAD (
	DELETE FROM contact_emails WHERE contact = OLD.con_id;
	DELETE FROM contacts WHERE id = OLD.con_id;
);

CREATE OR REPLACE RULE con_def_upd AS ON UPDATE TO contact_defaults
DO INSTEAD (
	UPDATE contact_emails SET email=NEW.email WHERE contact=OLD.con_id AND pri=0;
	UPDATE contacts SET full_name=NEW.full_name, con_type=NEW.con_type WHERE id=OLD.con_id;
);

CREATE OR REPLACE RULE con_def_ins AS ON INSERT TO contact_defaults
DO INSTEAD (
	INSERT INTO contacts (id,full_name,con_type) VALUES (NEW.con_id, NEW.full_name, NEW.con_type);
	INSERT INTO contact_emails (contact,pri,email) VALUES (NEW.con_id, 0, NEW.email); 
);
COMMIT;


-- Below are some queries to update the view and show what happens.
--
BEGIN;

SELECT * FROM contact_defaults ORDER BY con_id;
UPDATE contact_defaults SET con_type='BUS' WHERE con_id<4;
SELECT * FROM contact_defaults ORDER BY con_id;
UPDATE contact_defaults SET email=email || 'x' WHERE con_id>4;
SELECT * FROM contact_defaults ORDER BY con_id;

COMMIT;

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] convert timezone to string ...

[Marc G. Fournier]

On Tue, 25 Oct 2005, Tom Lane wrote:


"Marc G. Fournier" <[EMAIL PROTECTED]> writes:

I know that the server knows that ADT == -0400, and AST == -0300 ...


Other way around isn't it?  Unless Canada observes a pretty strange
variety of daylight saving time ;-)


I knew I was going to get that backwards :(


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(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: [SQL] automatic update or insert

[codeWarrior]

The following trigger procedure works for me you'd need to adjust this 
to manipulate YOUR table schema:

DROP FUNCTION dmc_comp_plan_duplicates() CASCADE;
CREATE OR REPLACE FUNCTION dmc_comp_plan_duplicates() RETURNS "trigger" AS
$BODY$

 DECLARE did integer;

 BEGIN

  SELECT COALESCE(id, 0) AS id FROM dmc_compensation_plan_quota
  WHERE dmc_compensation_plan = NEW.dmc_compensation_plan
  AND dmc_quota_item = NEW.dmc_quota_item
  INTO did;

  RAISE NOTICE 'DID: %', did;

  IF ((did = 0) OR (did IS NULL)) THEN

   RAISE NOTICE 'INSERT: DID: %', did;
   -- INSERT INTO dmc_compensation_plan_quota (dmc_compensation_plan, 
dmc_quota_item) VALUES (NEW.dmc_compensation_plan, NEW.dmc_quota_item);
   RETURN NEW;

  ELSE

   RAISE WARNING 'UPDATE: DID: %', did;
   UPDATE dmc_compensation_plan_quota SET active_flag = TRUE WHERE id = did;
   RETURN NULL;

  END IF;

  -- DEFAULT = DO NOTHING...
  RETURN NULL;

 END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION dmc_comp_plan_duplicates() OWNER TO datamosaics;
CREATE TRIGGER dmc_comp_plan_duplicates BEFORE INSERT ON 
dmc_compensation_plan_quota FOR EACH ROW EXECUTE PROCEDURE 
dmc_comp_plan_duplicates();


"tobbe" <[EMAIL PROTECTED]> wrote in message 
news:[EMAIL PROTECTED]
> Hi.
>
> I have a little problem.
>
> In a system of mine i need to insert records into table [tbStat], and
> if the records exist i need to update them instead and increase a
> column [cQuantity] for every update.
>
> I.e. the first insert sets cQuantity to 1, and for every other run
> cQuantity is increased.
>
> Currently i have implemented this as a stored procedure in the plpgsql
> language. This means that in my stored procedure i first do a select to
> find out if the row exists or not, then i do a insert or update
> depending if the row existed.
>
> Unfortunately, stored procedures seems awfully slow. And i need the
> application to go faster.
>
> One solution could be to implement the stored procedure in my program
> instead. I think that this will be atleast 50% faster than my stored
> procedure, so that would be ok.
>
> However, this has made me thinking. Couldn't this be done directly in
> SQL?
>
>
> Brgds Robert
> 



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Delete rule chain stops unexpectedly

[Wiebe Cazemier]

Tom Lane wrote:

So, the actual delete should be done after all the rules. And even if
it does delete before anything else, that does not explain why "step2"
is not inserted into the debuglog table.



Because the rule converts those inserts into, effectively,

INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row);

and there are no longer any matching OLD rows in the view.  (If it
didn't act that way then the INSERTs would execute even for a "DELETE
WHERE false".  If you find any of this surprising or not what you want,
you should probably be using triggers not rules.)


I've got a late addition question about this.

Is this behaviour also present in Postgres 7? I am continuing on work that was 
done by someone else in version 7. He said that what he made worked fine and 
tested OK, but with what you told me in mind, what he made can't possibly work 
(and it didn't when I tested it, and I have postgres 8).


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[SQL] 8.0.x windows installer fails?

[george young]

[Win XP Professional 2002, SP 1, Pentium 4, 1.4 GHz, 512MB RAM]
I'm trying to install postgres 8.0.4 on my windows machine.
I downloaded 
ftp.us.postgresql.org/pub/mirrors/postgresql/binary/v8.0.4/win32/postgresql-8.0.4.zip.
I then double-clicked the file "postgresql-8.0".
It says "Welcome to the PostgreSQL Installation Wizard...".
The default english language is ok, so...
I click the "Start" button, and immediately get a window labeled "Windows 
Installer" saying:

This installation package could not be opened.  Verify that the package exists 
adn that you can access it or contact the appilcation vendor to verify that 
this is a valid Windows Installer package.

So I tried the analgous file from 8.0.3, with the same results.
What am I doing wrong?

-- George Young
-- 
"Are the gods not just?"  "Oh no, child.
What would become of us if they were?" (CSL)

---(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: [SQL] 8.0.x windows installer fails?

[Magnus Hagander]

> [Win XP Professional 2002, SP 1, Pentium 4, 1.4 GHz, 512MB 
> RAM] I'm trying to install postgres 8.0.4 on my windows machine.
> I downloaded 
> ftp.us.postgresql.org/pub/mirrors/postgresql/binary/v8.0.4/win
> 32/postgresql-8.0.4.zip.
> I then double-clicked the file "postgresql-8.0".
> It says "Welcome to the PostgreSQL Installation Wizard...".
> The default english language is ok, so...
> I click the "Start" button, and immediately get a window 
> labeled "Windows Installer" saying:
> 
> This installation package could not be opened.  Verify that 
> the package exists adn that you can access it or contact the 
> appilcation vendor to verify that this is a valid Windows 
> Installer package.
> 
> So I tried the analgous file from 8.0.3, with the same results.
> What am I doing wrong?

You must uncompress the file first. If you just doubleclick inside the
ZIP file, it will only uncompress the file you doubleclicked on and not
the other MSI file also required.

Uncompress to a temp directory and doubleclick it from there.

//Magnus

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [SQL] Delete rule chain stops unexpectedly

[Tom Lane]

Wiebe Cazemier <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Because the rule converts those inserts into, effectively,
>> 
>> INSERT INTO debuglog SELECT ... WHERE EXISTS(some matching OLD row);
>> 
>> and there are no longer any matching OLD rows in the view.

> Is this behaviour also present in Postgres 7?

It's always been like that.  We've jiggered some details about the order
of rule firing, but not much else.  If you want a more detailed response
you need to be more specific about what version you're comparing to and
exactly what rules you're worried about.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[SQL] backend error

[Judith Altamirano Figueroa]

Hello everybody I have a failure making a select in a table the error that returns is the follow:

Backend message type 0x44 arrived while idle
pqReadData() -- backend closed the channel unexpectedly.
    This probably means the backend terminated abnormally
    before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

 How can I do to fix the failure?

[SQL] broken join optimization? (8.0)

[chester c young]

in php (for example) it's frequently nice to get the structure of a
table without any data, ie, pull a single row with each attribute's
value is null.  I use the query (dual is a table of one row ala
Oracle):

select m.* from dual
left join mytable m on( false );

this works every time, but if mytable is big, then takes a long time. 
needed to rewrite the query to:

select m.* from dual
left join (select * from mytable limit 1) m on( false );

this works as it should - pulls empty row but fast.

it seems to me that a full table scan should not be necessary if the
join condition is false.




__ 
Start your day with Yahoo! - Make it your home page! 
http://www.yahoo.com/r/hs

---(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

[SQL] ?Equiv to oracle (ENABLE|DISABLE) (CONSTRAINT|TRIGGER) statements?

[Bath, David]

Folks,

Summary:
  Does postgresql have equivalents to the following Oracle statements?
DISABLE CONSTRAINT ...
ENABLE CONSTRAINT ...
DISABLE TRIGGER ...
ENABLE TRIGGER ...

Background:
  One of the advantages of Oracle over some competitors such as MS-SQL
  and Sybase is the ability to toggle a constraint or trigger on and
  off, without blatting it, and without the hassle of finding any
  code and any accessory information (like comments, permissions...).

  BTW, I personally put C-style comments at the front of the clause so
  I can get the why's/how's into the syscatalogs - but I wear jackboots
  where documentation is concerned :-) and get at these for autodoccing
  and/or generation of meaningful messages to users when raising
  exception messages from the server.

  This capability is especially useful when there is some disgusting
  data-munging by a DBA, not just for import/export.

  I've tried grovelling through the sql from a pg_dump invoked with
  --disable-triggers, but it has no enable/disable triggers or
  constraints, merely creating primary/foreign constraints AFTER
  issuing the COPY.

  Yep, I'd expect this ONLY to work when issued by someone with DBA
  privs (and maybe the target object owner, although I imagine reasons
  that /might/ be a bad idea for paranoid info management governance).

Thanks in advance
-- 
David T. Bath
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] ?Equiv to oracle (ENABLE|DISABLE) (CONSTRAINT|TRIGGER) statements?

[Bruce Momjian]

Please see the 8.1 beta release notes for new capabilities in that
release.

---

Bath, David wrote:
> Folks,
> 
> Summary:
>   Does postgresql have equivalents to the following Oracle statements?
> DISABLE CONSTRAINT ...
> ENABLE CONSTRAINT ...
> DISABLE TRIGGER ...
> ENABLE TRIGGER ...
> 
> Background:
>   One of the advantages of Oracle over some competitors such as MS-SQL
>   and Sybase is the ability to toggle a constraint or trigger on and
>   off, without blatting it, and without the hassle of finding any
>   code and any accessory information (like comments, permissions...).
> 
>   BTW, I personally put C-style comments at the front of the clause so
>   I can get the why's/how's into the syscatalogs - but I wear jackboots
>   where documentation is concerned :-) and get at these for autodoccing
>   and/or generation of meaningful messages to users when raising
>   exception messages from the server.
> 
>   This capability is especially useful when there is some disgusting
>   data-munging by a DBA, not just for import/export.
> 
>   I've tried grovelling through the sql from a pg_dump invoked with
>   --disable-triggers, but it has no enable/disable triggers or
>   constraints, merely creating primary/foreign constraints AFTER
>   issuing the COPY.
> 
>   Yep, I'd expect this ONLY to work when issued by someone with DBA
>   privs (and maybe the target object owner, although I imagine reasons
>   that /might/ be a bad idea for paranoid info management governance).
> 
> Thanks in advance
> -- 
> David T. Bath
> [EMAIL PROTECTED]
> 
> 
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
> 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  pgman@candle.pha.pa.us   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] broken join optimization? (8.0)

[Michael Fuhr]

On Tue, Oct 25, 2005 at 04:56:11PM -0700, chester c young wrote:
> in php (for example) it's frequently nice to get the structure of a
> table without any data, ie, pull a single row with each attribute's
> value is null.  I use the query (dual is a table of one row ala
> Oracle):
> 
> select m.* from dual
> left join mytable m on( false );

Have you considered "SELECT * FROM mytable LIMIT 0"?  APIs typically
allow you to find out the row structure even if no rows were returned.
In recent versions of PHP, for example, you can use pg_num_fields(),
pg_field_name(), pg_field_type(), etc., or perhaps the experimental
pg_meta_data().

> this works every time, but if mytable is big, then takes a long time. 

I see the same behavior in the latest 8.1beta code.  Maybe one of
the developers will comment on whether optimizing that is a simple
change, a difficult change, not worth changing because few people
find a use for it, or a behavior that can't be changed because of
something we're not considering.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] backend error

[Michael Fuhr]

On Tue, Oct 25, 2005 at 06:28:38PM -0500, Judith Altamirano Figueroa wrote:
> Hello everybody I have a failure making a select in a table the error
> that returns is the follow:
> 
> Backend message type 0x44 arrived while idle
> pqReadData() -- backend closed the channel unexpectedly.
> This probably means the backend terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.

What version of PostgreSQL are you using?  What operating system
and version?  What's the query?  What appears in the server log
when you get the error?  Did you get a core dump, and if so, can
you get a stack trace from it?

-- 
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: [SQL] broken join optimization? (8.0)

[Tom Lane]

Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Tue, Oct 25, 2005 at 04:56:11PM -0700, chester c young wrote:
>> in php (for example) it's frequently nice to get the structure of a
>> table without any data,

> Have you considered "SELECT * FROM mytable LIMIT 0"?

Indeed.

> I see the same behavior in the latest 8.1beta code.  Maybe one of
> the developers will comment on whether optimizing that is a simple
> change, a difficult change, not worth changing because few people
> find a use for it, or a behavior that can't be changed because of
> something we're not considering.

Not worth changing --- why should we expend cycles (even if it only
takes a few, which isn't clear to me offhand) on every join query, to
detect what's simply a brain-dead way of finding out table structure?
I can't think of any realistic scenarios for a constant-false join
clause.

The relevant bit of code is in initsplan.c:

/*
 * If the clause is variable-free, we force it to be evaluated at its
 * original syntactic level.  Note that this should not happen for
 * top-level clauses, because query_planner() special-cases them.  But it
 * will happen for variable-free JOIN/ON clauses.  We don't have to be
 * real smart about such a case, we just have to be correct.
 */
if (bms_is_empty(relids))
relids = qualscope;

Possibly you could get the planner to generate a gating Result node for
such a case, the way it does for constant-false top level WHERE clauses,
but I really doubt it's worth any extra cycles at all to make this
happen.  The proposed example is quite unconvincing ... why would anyone
want to depend on the existence of a "dual" table rather than LIMIT 0?

regards, tom lane

---(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

[SQL] pl/* overhead ...

[Marc G. Fournier]

Does anyone know of, or have, any comparisions of the overhead going with 
something like pl/perl or pl/php vs using pl/pgsql?


Thanks ...


Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 6: explain analyze is your friend

[SQL] padding an integer ...

[Marc G. Fournier]

Is there any way of "padding" an integer, similar to how, in perl, I would 
do:


printf("%03d", 1);

to get:

001

Specifically, I'm looking to do this in a pl/pgsql function ... like to 
avoid moving to pl/php or pl/perl if I can ... but, from what I've been 
able to find, I suspect I'm not going to have much of a choice ...



Marc G. Fournier   Hub.Org Networking Services (http://www.hub.org)
Email: [EMAIL PROTECTED]   Yahoo!: yscrappy  ICQ: 7615664

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [SQL] padding an integer ...

[Michael Fuhr]

On Wed, Oct 26, 2005 at 12:57:25AM -0300, Marc G. Fournier wrote:
> Is there any way of "padding" an integer, similar to how, in perl, I would 
> do:
> 
> printf("%03d", 1);
> 
> to get:
> 
> 001

test=> SELECT to_char(1, '000');
 to_char 
-
  001
(1 row)

http://www.postgresql.org/docs/8.0/interactive/functions-formatting.html

-- 
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

[SQL] why vacuum

[Kenneth Gonsalves]

hi,
i was in a minor flame war with a mysql guy - his major grouse was that 
'I wouldnt commit mission critical data to a database that needs to be 
vacuumed once a week'. So why does pg need vacuum?
-- 
regards
kg

http://www.livejournal.com/users/lawgon
tally ho! http://avsap.org.in
ಇಂಡ್ಲಿನಕ್ಸ வாழ்க!

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] why vacuum

[Tom Lane]

Kenneth Gonsalves <[EMAIL PROTECTED]> writes:
> i was in a minor flame war with a mysql guy - his major grouse was that 
> 'I wouldnt commit mission critical data to a database that needs to be 
> vacuumed once a week'.

This guy is not worth arguing with.

> So why does pg need vacuum?

Every database needs maintenance operations.  PG is designed in a way
that exposes the maintenance operations to the control of the DBA a bit
more than most other DBMSes do: specifically, you get to decide when
some of the overhead work happens.  We think this is a feature, because
you can schedule the overhead for low-activity periods (nights,
weekends, whatever).  In other DBMSes the equivalent work happens as
part of foreground queries, no matter how time-critical they might be.

Now, there's no doubt that for a database run by a non-expert person
who can't even spell DBA, exposing this sort of knob isn't very helpful.
So there's work afoot to provide automatic maintenance tools (ie,
autovacuum).  Over time I think autovacuum will get smart enough that
even experts will usually use it.  But that point will only be reached
when autovacuum has some idea about doing more work during low-load
periods.

Unless MySQL invents some concept equivalent to VACUUM, they won't have
any prayer at all of being able to shift maintenance overhead to
low-load times.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] why vacuum

[Michael Fuhr]

On Wed, Oct 26, 2005 at 10:15:17AM +0530, Kenneth Gonsalves wrote:
> i was in a minor flame war with a mysql guy - his major grouse was that 
> 'I wouldnt commit mission critical data to a database that needs to be 
> vacuumed once a week'.

The use of the word "commit" is amusing, considering that MySQL's
default table type doesn't support transactions.  There's always
InnoDB, but it seems like there was something about that in the
news recently

Compare the following lists of gotchas and decide which database
*you'd* commit mission-critical data to:

http://sql-info.de/mysql/gotchas.html
http://sql-info.de/postgresql/postgres-gotchas.html

> So why does pg need vacuum?

See the documentation:

http://www.postgresql.org/docs/8.0/interactive/maintenance.html#ROUTINE-VACUUMING

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org

Re: [SQL] why vacuum

[Bath, David]

On Wed, 26 Oct 2005 15:14, Tom Lane wrote:
  > Kenneth Gonsalves <[EMAIL PROTECTED]> writes:
  > > (A MySQul guy said, not Kenneth)...
  > > 'I wouldnt commit mission critical data to a database that needs to be 
  > > vacuumed once a week'.
My two-penneth worth...
I wouldn't commit mission critical data to a database (or DBA) that doesn't
have a concept of vacuuming (or desire to do it regularly).  But, less
flamingly, I wouldn't commit mission-critical data to something that lacked
the ability to have proper constraints, triggers and server-side procedures
to ensure the data actually remains sensible.  Note that Sybase/MS-SQL's
check constraint model asserts the constraint BEFORE the trigger, which
discourages you from attempting to check and handle meaning of data!

  > This guy is not worth arguing with.
D'Accord!

  > > So why does pg need vacuum?
For (inter alia) the same reason that
* Oracle has an ANALYZE_SCHEMA and DBMS_SPACE_ADMIN
and (hoist by his own petard)
* MySQuaL has myisamchk --stats_method=method_name --analyze

Oh, well: MySQL bigot and internal consistency? whadya 
expect?
 
Dave Bath
(Oracle DBA for health/telcos way back in 1986: honeywrong GCOS and Pr1mos)
-- 
David T. Bath
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org