I think you can get what you want if you change the rule definition to
CREATE RULE
people_upd_history AS ON UPDATE TO people
DO INSERT INTO
people_history
SELECT * FROM only people WHERE usr_pkey = old.usr_pkey;
and your updates to be only's
-- update table (1) -- 2
UPDATE ONLY people SET color
I thought that all rows in inherited tables are visible to the table
that they are inherited from (ie all rows in people_history are visible
to people).
step by step would be
> INSERT INTO people (usr_name, color) VALUES ('bob', 'red');
make one row in people
> -- update table (1) -- 2
> UPDA
While I would agree with you that from a purely technical standpoint, the
user inserted into the database a CRLF and a query with just an LF does not
exactly match that, from a users and more practical perspective, that does
not make sense at all. That is why I surrounded the word bug in ??.
I wo
Title: RE: [GENERAL] PHP SQL
Thanks for your response.
It is as you mentioned, within the same database.
I will get working on a stored procedure this morning.
Thanks for your response.
Kind Regards,
Shaun Clements
-Original Message-
From: Jeff Davis [mailto:[EMAIL PROTECTED]]
S
On Tue, Mar 22, 2005 at 09:59:23PM -0800, Scott Frankel wrote:
> What is the proper syntax for using FROM ONLY table_name in an UPDATE
> statement?
See the UPDATE documentation:
http://www.postgresql.org/docs/8.0/static/sql-update.html
> What is the proper syntax for specifying FROM ONLY in th
Did you happen to look at the manual?
http://www.postgresql.org/docs/7.4/static/sql-update.html
It pretty clearly indicates that the syntax is UPDATE ONLY table, so
try eliminating the FROM in your UPDATE example below.
http://www.postgresql.org/docs/7.4/static/sql-createtable.html
The CREATE TAB
Added to TODO:
o Add MOVE to PL/pgSQL
---
FERREIRA William (COFRAMI) wrote:
> does i hope that CURSOR will be fully implemented soon ?
> (or maybe you know an alternative for my problem ? :) )
>
> -Message d'o
Syntax troubles.
What is the proper syntax for using FROM ONLY table_name in an UPDATE
statement?
According to the docs, In a FROM clause, I should be able to use the
ONLY keyword
preceding the table name. This throws an error:
UPDATE FROM ONLY people SET color = 'cyan' WHERE usr_pkey =
On Mon, Mar 21, 2005 at 10:58:27AM -0500, Stephen Howie wrote:
> I've notice a couple things. I ran a couple test and at first I
> couldn't duplicate my error on some test tables. But I then added
> inheritance to one of the tables and thats when I got the error again.
Are you aware that fo
Michael Fuhr <[EMAIL PROTECTED]> writes:
> I can duplicate the crash in 8.0.1 (REL8_0_STABLE) with the following
Grumble ... I seem to have managed to promote intagg from
broken-on-64bit-platforms to broken-on-every-platform ...
will look into a fix tomorrow.
regards, tom
Both the 7.4.x docs and the 8.0.x docs state:
"This option can only be set at server start or in the postgresql.conf
file."
Perhaps I've been misunderstanding the "or" clause? Does the "or"
clause refer to the fact that it can be sent as an option at server
start or changed with a HUP from post
Lonni J Friedman wrote:
On Tue, 22 Mar 2005 17:51:06 -0800, Randy Samberg
<[EMAIL PROTECTED]> wrote:
Does anyone know if there is a way in Postgres to emulate Oracle, in other
words make Postgres think it is an Oracle database? If so, do you have any
idea what percentage of people are doing thi
Greg Stark wrote:
> Bruce Momjian writes:
>
> > I don't see something that happens every five minutes as any kind of
> > performance problem. I am not sure what Josh saw that made him want to
> > increase that.
>
> I would have thought checkpoint_timeout would be something you would adjust
> d
Bruce Momjian writes:
> I don't see something that happens every five minutes as any kind of
> performance problem. I am not sure what Josh saw that made him want to
> increase that.
I would have thought checkpoint_timeout would be something you would adjust
depending on whether you want even
On Tue, Mar 22, 2005 at 05:21:32PM -0800, Ron Mayer wrote:
>
> If one of the contrib modules (int_array_aggregate in contrib/intagg) is
> crashing my backend, where's a good place to report a bug or get hints
> where to look further?
Bug reports should go to the pgsql-bugs mailing list (I've cc
On Tue, Mar 22, 2005 at 09:28:17PM -0500, Carlos Moreno wrote:
> >I guess what changed from version 7.4.x to 8.0 is that the default
> >server_encoding changed?
>
> Some further digging through the documentation reveals (in addition
> to the fact that I seem to be an incompetent when it comes to
"Thomas F.O'Connell" <[EMAIL PROTECTED]> writes:
> Instinctively, it seems like it would be nice to have something similar
> for checkpoint_timeout, but is there any disadvantage to having a value
> "dramatically" higher than the default for general use, considering
> that checkpoint_timeout can
Ok,
here is a example
CREATE OR REPLACE FUNCTION mytest();
RETURNS VOID as
$$
DECLARE
mytestvar varchar;
mytestvar2 integer;
BEGIN
mytestvarr = 'bla';
select testfield from nonexistanttable where testfield = 2
INTO mytestvar2;
--The table does not exits, yet postgresql d
On Tue, 22 Mar 2005, Scott Frankel wrote:
>
> This is weird. I have two tables: one inherits from the other. And I
> have a
> rule that populates the inherited table with changes from the first.
> When I
> update a row in the first table, I get an ever-larger number of rows
> added to
> both i
Thomas F.O'Connell wrote:
> In the annotated conf file for 8.0, Josh Berkus notes that
> checkpoint_timeout should be increased "dramatically" for large data
> loads. For settings like sort_mem (now work_mem), it's been convenient
> to be able to have a default setting for standard work and a
>
PostgreSQL and Oracle are both relational databases.
PostgreSQL and Oracle both use a very similar and interesting lock strategy for
dealing with sets called MVCC.
PostgreSQL and Oracle can both be accessed in complete a completely generic way
using ODBC, OLEDB, JDBC, or a .NET provider.
Postg
[...]
This makes sense to me, yes. The reason why I'm a bit lost is that
we never did anything whatsoever with respect to encoding. Oddly
enough, I couldn't find much about this in the docs.
[...]
I guess what changed from version 7.4.x to 8.0 is that the default
server_encoding changed?
Some f
Randy Samberg wrote:
> Does anyone know if there is a way in Postgres to emulate Oracle, in
> other words make Postgres think it is an Oracle database? If so, do
> you have any idea what percentage of people are doing this, and how
> successful they are with this? Also, how is this done? Do you
On Tue, 22 Mar 2005 17:51:06 -0800, Randy Samberg
<[EMAIL PROTECTED]> wrote:
>
> Does anyone know if there is a way in Postgres to emulate Oracle, in other
> words make Postgres think it is an Oracle database? If so, do you have any
> idea what percentage of people are doing this, and how succes
This is weird. I have two tables: one inherits from the other. And I
have a
rule that populates the inherited table with changes from the first.
When I
update a row in the first table, I get an ever-larger number of rows
added to
both it and the inherited table. i.e.:
update 1 yiel
Does anyone know if
there is a way in Postgres to emulate Oracle, in other words make Postgres think
it is an Oracle database? If so, do you have any idea what percentage of
people are doing this, and how successful they are with this? Also, how
is this done? Do you know of any good link
If one of the contrib modules (int_array_aggregate in contrib/intagg) is
crashing my backend, where's a good place to report a bug or get hints
where to look further?
It seems to work fine on small arrays, but crashes on large ones.
The second query would have put about 500 rows in the aggrega
On Mar 22, 2005, at 12:55 PM, Konstantinos Agouros wrote:
does this work in any way (PG 7.4.7 and OSX 10.3), so I can access
postgres
as data source from say excel?
I don't have any recent versions of Excel, but PostgreSQL should work
with ODBC on the Mac. You'll find the ODBC administrator appli
Tom Lane <[EMAIL PROTECTED]> writes:
> Greg Stark <[EMAIL PROTECTED]> writes:
> > Is it enough to just do
> > update pg_constraint set condeferrable = 't' where contype = 'f';
>
> I think you'd need to start a fresh backend session --- the relcache
> entries for the tables probably won't notic
In the annotated conf file for 8.0, Josh Berkus notes that
checkpoint_timeout should be increased "dramatically" for large data
loads. For settings like sort_mem (now work_mem), it's been convenient
to be able to have a default setting for standard work and a
per-connection configuration option
> pg_fetch_row
> pg_fetch_assoc
> pg_fetch_object
> pg_fetch_result
> pg_fetch_array
>
> Are there performance differences, which are noticeable?
I doubt you'll see much real difference. Your real bottleneck will be
I/O.
You might be able to save something by using COPY out and then back i
On Tuesday March 22 2005 1:32, Qingqing Zhou wrote:
>
> Sorry, forget to mention this project:
> http://pqa.projects.postgresql.org/example.html
Ah, that's interesting. I overlooked that post earlier since I
wasn't CC'd. Thanks!
Ed
---(end of broadcast)---
On Monday March 21 2005 4:30, Ed L. wrote:
> I'm attempting to measure database query performance across a
> large number of high-volume clusters
Let me try rephrasing this: Is anyone systematically measuring
query execution times across multiple clusters to identify
performance issues in a
Tom Lane wrote:
> Peter Eisentraut <[EMAIL PROTECTED]> writes:
> > Implementing this type of syntax checker isn't hard since the code
> > is already there, but there might be a small, ugly problem. IIRC,
> > the parser and/or semantic analyzer of PL/pgSQL relies on knowing
> > whether the function
Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Implementing this type of syntax checker isn't hard since the code is
> already there, but there might be a small, ugly problem. IIRC, the
> parser and/or semantic analyzer of PL/pgSQL relies on knowing whether
> the function is called as a trigger
Greg Stark <[EMAIL PROTECTED]> writes:
> Is it enough to just do
> update pg_constraint set condeferrable = 't' where contype = 'f';
I think you'd need to start a fresh backend session --- the relcache
entries for the tables probably won't notice the above hack.
regards
Alvaro Herrera wrote:
> On Tue, Mar 22, 2005 at 06:09:28PM +0100, Peter Eisentraut wrote:
> > [EMAIL PROTECTED] wrote:
> > > currently when you execute a CREATE OR REPLACE FUNCTION there is
> > > virtually no checking of the function body for variable type
> > > compatibility or if the SQL statemen
Alvaro Herrera <[EMAIL PROTECTED]> writes:
> alvherre=# create function test_syntax() returns void language plpgsql as
> 'begin zelect 1; return; end';
> CREATE FUNCTION
> alvherre=# select test_syntax();
> ERROR: error de sintaxis en o cerca de «zelect» en el carácter 1
> QUERY: zelect 1
> CONT
I want all my foreign key constraints to be deferrable. They were all created
with the default (not deferrable).
Is it enough to just do
update pg_constraint set condeferrable = 't' where contype = 'f';
?
It doesn't seem to be enough. I still get constraint violations as soon as I
try to de
Sean Davis <[EMAIL PROTECTED]> writes:
> On Mar 22, 2005, at 7:26 AM, Szmutku Zoltán wrote:
>> I would like to: server ignore the bad statements (return false) and
>> after I call rollback or commit manuallypossible ?
> Within a transaction, if one statement fails, all changes will be
> roll
[EMAIL PROTECTED] writes:
> I am getting the error:
> "ERROR: record type has not been registered"
> CONTEXT: PL/pgSQL function "f_cmplx_prdct_dlte" while storing call
> arguments into local variables
This is probably a bug. Could you provide a self-contained example
that causes it? Prefera
Hi,
does this work in any way (PG 7.4.7 and OSX 10.3), so I can access postgres
as data source from say excel?
Konstantin
--
Dipl-Inf. Konstantin Agouros aka Elwood Blues. Internet: elwood@agouros.de
Otkerstr. 28, 81547 Muenchen, Germany. Tel +49 89 69370185
-
On Tue, Mar 22, 2005 at 06:09:28PM +0100, Peter Eisentraut wrote:
> [EMAIL PROTECTED] wrote:
> > currently when you execute a CREATE OR REPLACE FUNCTION there is
> > virtually no checking of the function body for variable type
> > compatibility or if the SQL statements are valid.
>
> Only for ver
[EMAIL PROTECTED] wrote:
Hi, currently when you execute a CREATE OR REPLACE FUNCTION there is
virtually no checking of the function body for variable type
compatibility or if the SQL statements are valid. It's not a big deal
for long time users of Postgres, but for users migrating from systems
such
[EMAIL PROTECTED] wrote:
> currently when you execute a CREATE OR REPLACE FUNCTION there is
> virtually no checking of the function body for variable type
> compatibility or if the SQL statements are valid.
Only for very small values of "no". Please provide an example.
--
Peter Eisentraut
http
On Tuesday March 22 2005 9:36, Vivek Khera wrote:
> While poking through the logs for these errors, I'm finding a
> *lot* of broken pipe/unexpected EOF errors for this server but
> for connections from other hosts as well, running reports.
> those hosts still have the 7.4 client libraries.
You mi
On Mar 22, 2005, at 11:47 AM, Scott Marlowe wrote:
The same config on the old box with Pg 7.4.7 worked flawlessly for
running reports and dumps. Another issue is that the 8.0 server is
noticeably slower than the 7.4 with identically (translated to 8.0
style) configs.
IS there a difference in the i
On Mar 22, 2005, at 11:53 AM, Ed L. wrote:
You might look into possible network-related issues. Maybe check
what is happening on the NICs (tx/rx errors? collisions?), see
if that gives any clues...
well, whaddya know: kernel log messages at the relevant times:
Mar 22 03:42:22 d01 kernel: bge0: w
On Tue, 2005-03-22 at 10:36, Vivek Khera wrote:
> This morning I came in to discover that my nightly pg_dump backup to a
> remote server had failed. Both the server and the client are have
> postgres 8.0.1 installed. Figuring it was just a fluke I ran the dump
> again by hand and got the same
This morning I came in to discover that my nightly pg_dump backup to a
remote server had failed. Both the server and the client are have
postgres 8.0.1 installed. Figuring it was just a fluke I ran the dump
again by hand and got the same error:
% pg_dump -h d01-prv -Fc -f mm.22-Mar-2005.dump
Hi,
currently when you execute a CREATE OR REPLACE FUNCTION there is virtually no
checking of the function body for variable type compatibility or if the SQL
statements are valid.
It's not a big deal for long time users of Postgres, but for users migrating
from systems such as MS SQL or Oracle
Shaun Clements wrote:
Excellent idea. Yes, I would prefer to write in plpgsql.
My next concern then, is, does Postgres, perform some kind of Load
Balancing.
Well, you can have multiple clients running queries at the same time.
I dont want the stored procedure once run, to DOS other users, who are
r
On Mon, 21 Mar 2005, Andrew J. Kopciuch wrote:
> If you look at your config mappings for the "default_english" you will notice
> that you have 16 records, as opposed to 19 records like every other
> configuration mapping. From some more in depth observations, I noticed you
> are missing entries fo
Am Dienstag, 22. März 2005 08:00 schrieb Sim Zacks:
> While I was testing an issue in python I discovered a general
> *nix/windows/mac issue with newlines.
> The same query will give different results depending on what client
> executes it.
While that is indoubtedly strange behavior, I can't see h
On Tue, Mar 22, 2005 at 04:19:06PM +1100, Neil Conway wrote:
> Edwin New wrote:
> >I have encountered a situation where a java process is dying but leaving
> >locks active.
>
> So it seems that the problem is that when the client dies, it is not
> actually disconnecting from PostgreSQL, and is i
On Tue, Mar 22, 2005 at 07:41:50AM -0500, Sean Davis wrote:
>
> Within a transaction, if one statement fails, all changes will be
> rolled back. If you want to commit each statement that works, you can
> commit (or rollback) after each statement.
...or use savepoints (new in PostgreSQL 8.0).
On Mon, Mar 21, 2005 at 12:39:09PM -0800, TJ O'Donnell wrote:
> I have N-bit data pairs. I want to write a c-language function
> which compares bits set in each. N is typically 512, but could be other.
> I can store this as bit varying(512) or bytea(64). I can't decide which.
> Here are the que
On Tue, Mar 22, 2005 at 17:47:48 +0530,
Rajarshi Mukherjee <[EMAIL PROTECTED]> wrote:
> PLEASE GIVE ME AN EXAMPLE..
Please read the documentation of the CREATE FUNCTION command.
>
>
> On Tue, 22 Mar 2005 06:24:58 -0600, Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> > On Tue, Mar 22, 2005 at 17
On Mar 22, 2005, at 7:17 AM, Rajarshi Mukherjee wrote:
PLEASE GIVE ME AN EXAMPLE..
Just add the "SECURITY DEFINER" keyword to the function definition.
http://www.postgresql.org/docs/8.0/interactive/sql-createfunction.html
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
On Mar 22, 2005, at 7:26 AM, Szmutku Zoltán wrote:
Hi everybody ,
I try using Postgre, but I have some problems.
I create a constraint ( R1>=0 ), and after connect to server from
VFP via ODBC .
In the client program I turn on the transactions . (
SQLSETPROP(nHandle,'Transactions',2) )
W
On Tue, 22 Mar 2005 11:44:00 +, Charlotte Pollock
<[EMAIL PROTECTED]> wrote:
> Hi all
>
> I want to export data from my postgres database.
>
> I want to output, for each record, a folder containing an XML file eg
>
> qualifier="author">Pollock, Charlotte
>
Rajarshi Mukherjee wrote:
Hello all,
i have a function that updates a table. I gave execute grant on it to
a particular user but no grant on the table in question.
when logging in as that user and executing the function, i get an error
ERROR: permission denied for relation
How can i en
Hi everybody ,
I try using Postgre, but I have some
problems.
I create a constraint ( R1>=0 ), and after
connect to server from VFP via ODBC .
In the client program I turn on the transactions .
( SQLSETPROP(nHandle,'Transactions',2) )
When I run UPDATE statements one after the oth
Hi all,
Version 1.0.0.19 has been released. This provides better backend mode
detection for time and timestamp handling. With this version all known
type related problems are now fixed. I repeat - if you have been
postponing checking out OLE DB because you have heard that it is still
alpha, now
Title: RE: [GENERAL] PHP SQL
Excellent idea. Yes, I would prefer to write in plpgsql.
My next concern then, is, does Postgres, perform some kind of Load Balancing.
I dont want the stored procedure once run, to DOS other users, who are running reports.. or perform queries using some application
PLEASE GIVE ME AN EXAMPLE..
On Tue, 22 Mar 2005 06:24:58 -0600, Bruno Wolff III <[EMAIL PROTECTED]> wrote:
> On Tue, Mar 22, 2005 at 17:27:25 +0530,
> Rajarshi Mukherjee <[EMAIL PROTECTED]> wrote:
> > Hello all,
> >
> > i have a function that updates a table. I gave execute grant on it to
> >
On Tue, Mar 22, 2005 at 17:27:25 +0530,
Rajarshi Mukherjee <[EMAIL PROTECTED]> wrote:
> Hello all,
>
> i have a function that updates a table. I gave execute grant on it to
> a particular user but no grant on the table in question.
>
> when logging in as that user and executing the functi
On Tue, Mar 22, 2005 at 01:25:30PM +0200, Shaun Clements wrote:
> Which is more efficient, in reading in a row of data, and assigning
> individual column values to variables, and then inserting a new row into the
> new table.
>
> pg_fetch_row
> pg_fetch_assoc
> pg_fetch_object
> pg_fetch_result
>
Hello all,
i have a function that updates a table. I gave execute grant on it to
a particular user but no grant on the table in question.
when logging in as that user and executing the function, i get an error
ERROR: permission denied for relation
How can i ensure that any update on th
On Mar 22, 2005, at 3:13 AM, FERREIRA William (COFRAMI) wrote:
my function is very long but i found an example with the same
comportment :
CREATE OR REPLACE FUNCTION adoc.totoTest()
RETURNS int4 AS
$BODY$
my $var = '->>>';
&concat($var);
sub concat {
$var .= 'tagada';
}
elog NOTICE, $var
Hi all
I want to export data from my postgres database.
I want to output, for each record, a folder containing an XML file eg
Pollock, Charlotte
asdasdasdasd
Our
Publication
Book
each folder (and the xml file contained within
FERREIRA William (COFRAMI) wrote:
my function is very long but i found an example with the same comportment :
CREATE OR REPLACE FUNCTION adoc.totoTest()
RETURNS int4 AS
$BODY$
my $var = '->>>';
&concat($var);
sub concat {
$var .= 'tagada';
}
elog NOTICE, $var;
return 4;
$BODY$
LANGUAGE
Title: [GENERAL] PHP SQL
Hi All
I am designing a data loader script that can transform data in one table schema into a new table schema in Postgres for reporting purposes.
I wanted to ask, on a performance issue.
Which is more efficient, in reading in a row of data, and assigning individua
Hi,
Can anyone explain or suggest a workaround for the following:
I am getting the error:
"ERROR: record type has not been registered"
CONTEXT: PL/pgSQL function "f_cmplx_prdct_dlte" while storing call arguments into local variables
when I call a function from a delete rule for a view I've
Title: RE: [GENERAL] Convert Cursor to array
my
function is very long but i found an example with the same comportment
:
CREATE
OR REPLACE FUNCTION adoc.totoTest() RETURNS int4
AS$BODY$ my $var =
'->>>'; &concat($var);
sub concat { $var .=
'tagada'; } elog NOTICE, $var; return 4;
$BODY
"Qingqing Zhou" <[EMAIL PROTECTED]> writes
> ""Ed L."" <[EMAIL PROTECTED]> writes
> > How are others handling this problem? Other ideas?
> >
>
Sorry, forget to mention this project:
http://pqa.projects.postgresql.org/example.html
Regards,
Qingqing
---(end of broadca
""Ed L."" <[EMAIL PROTECTED]> writes
> How are others handling this problem? Other ideas?
>
In your client, you will measure the libpq/odbc/jdbc or whatever call's
completion time and send the interesting parts to your log database. And
just make sure the tested database and the log database are
77 matches
Mail list logo
