"Guy Rouillier" <[EMAIL PROTECTED]> writes:
> We appear to have two issues here:
> (1) What is the meaning of "replace" as in replace view?
> (2) What are the semantics for multiple set operations?
> My original issue deals with (1). I'm unfortunately not well versed in
> the SQL spec, but from
Tom and Stephan, thank you both for taking the time to reply. Further
comments inline.
Tom Lane wrote:
> Stephan Szabo <[EMAIL PROTECTED]> writes:
>> On Fri, 11 Feb 2005, Guy Rouillier wrote:
>>> Now I create a view like this:
>>> create or replace view v1 as select * from t1;
>>>
>>> Next, I at
Try using the "~" regex matching operator instead of ILIKE.
Regards,
Jeff Davis
On Fri, 2005-02-11 at 22:21 +, fiona wrote:
> My database table holds phone numbers that may contain characters other
> than digits (that's not a problem in itself).
>
> I want to be able to apply a regu
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Fri, Feb 11, 2005 at 03:37:01PM -0500, Tom Lane wrote:
>> [ thinks about that for awhile... ] Oh, I see. The reason this appears
>> to work is that plpgsql compiles a separate version of the function for
>> each actual parameter datatype that is used
Miles Keaton <[EMAIL PROTECTED]> writes:
> When I do a pg_dump, (--data-only), PG7 used to dump the data out in
> order, so that all foreign-key checks worked correctly when loading
> the data back in.
If it did so, it was by pure luck.
In general it is not possible for pg_dump to guarantee this
On Fri, Feb 11, 2005 at 03:37:01PM -0500, Tom Lane wrote:
> [ thinks about that for awhile... ] Oh, I see. The reason this appears
> to work is that plpgsql compiles a separate version of the function for
> each actual parameter datatype that is used in a given session. So in
> your example, yo
When I do a pg_dump, (--data-only), PG7 used to dump the data out in
order, so that all foreign-key checks worked correctly when loading
the data back in.
Now it seems with PG8 it's dumping it completely out of order (one of
my completely foreign-key join tables first!) - and I can't get it to
dum
On Fri, 11 Feb 2005 20:40:18 -0500, Tom Lane <[EMAIL PROTECTED]> wrote:
> Sven Willenberger <[EMAIL PROTECTED]> writes:
> > plperl.c: In function `compile_plperl_function':
> > plperl.c:541: warning: cast to pointer from integer of different size
> > plperl.c:730: warning: cast from pointer to inte
Sven Willenberger <[EMAIL PROTECTED]> writes:
> plperl.c: In function `compile_plperl_function':
> plperl.c:541: warning: cast to pointer from integer of different size
> plperl.c:730: warning: cast from pointer to integer of different size
BTW, these scare the heck out of me. It looks to me like
Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Fri, 11 Feb 2005, Guy Rouillier wrote:
>> Now I create a view like this:
>> create or replace view v1 as select * from t1;
>>
>> Next, I attempt to update this view like this:
>> create or replace view v1 as select * from t1 union select * from t2;
>>
On Fri, 2005-02-11 at 19:54 -0500, Tom Lane wrote:
> Sven Willenberger <[EMAIL PROTECTED]> writes:
> > The rpath solution you provided to patch GNUmakefile did not
> > work however, bailing with:
>
> > gcc -O2 -fno-strict-aliasing -Wmissing-declarations, -Wl,
> > -rpath, /usr/local/lib/perl5/5.8.6
Sven Willenberger <[EMAIL PROTECTED]> writes:
> The rpath solution you provided to patch GNUmakefile did not
> work however, bailing with:
> gcc -O2 -fno-strict-aliasing -Wmissing-declarations, -Wl,
> -rpath, /usr/local/lib/perl5/5.8.6/mach/CORE -fpic -DPIC -I.
> -I/usr/local/lib/perl5/5.8.6/mach/
On Fri, 11 Feb 2005, Guy Rouillier wrote:
> I using 8.0.1. I create 3 tables with these definitions:
>
> create table t1 (serv_id varchar(50) not null);
> create table t2 (serv_id varchar(50) not null);
> create table t3 (serv_id varchar(50) not null);
>
> Now I create a view like this:
>
> crea
I using 8.0.1. I create 3 tables with these definitions:
create table t1 (serv_id varchar(50) not null);
create table t2 (serv_id varchar(50) not null);
create table t3 (serv_id varchar(50) not null);
Now I create a view like this:
create or replace view v1 as select * from t1;
Next, I attempt
It's just an individual backend. Unfortunately I don't have the postgres
log file yet, which would obviously help - I only have the application
client's log, but the people reporting the problem know they need to get
me the database log file next time it happens. I haven't been able to
repro this o
On Fri, 2005-02-11 at 18:35 -0500, Tom Lane wrote:
> Sven Willenberger <[EMAIL PROTECTED]> writes:
> > it looks like the sed line is stripping out pgac_tmp2 from the pgac_tmp1
> > leaving the system to use the default perl libperl.so.
> > when I modified perl_embed_ldflags to not remove the -R line
"David Parker" <[EMAIL PROTECTED]> writes:
> OK, well that's good to know. You mentioned ulimit in
> http://archives.postgresql.org/pgsql-bugs/2003-12/msg00080.php
Well, that was a different scenario --- or at least I thought it was.
Are you seeing unwanted shutdown of the entire database cluster
Sven Willenberger <[EMAIL PROTECTED]> writes:
> it looks like the sed line is stripping out pgac_tmp2 from the pgac_tmp1
> leaving the system to use the default perl libperl.so.
> when I modified perl_embed_ldflags to not remove the -R line, the
> compile works and links to the proper libperl.so
I
OK, well that's good to know. You mentioned ulimit in
http://archives.postgresql.org/pgsql-bugs/2003-12/msg00080.php
which ulimit parameters were you thinking of? That post is what set me
barking up
this tree ;-) The only other thing not set to "unlimited" is stack,
which is set to
8480 for the
"David Parker" <[EMAIL PROTECTED]> writes:
> We have started getting the error
>FATAL: terminating connection due to administrator command
> in some of our processes. Searching in the archives, I gather that this
> is caused by a SIGTERM, and might be coming from a ulimit problem.
It is comin
On Fri, 2005-02-11 at 23:20 +0100, Anton Berezin wrote:
> On Fri, Feb 11, 2005 at 11:09:35PM +0100, Anton Berezin wrote:
> > On Fri, Feb 11, 2005 at 04:41:55PM -0500, Sven Willenberger wrote:
> > > On Fri, 2005-02-11 at 21:35 +0100, Anton Berezin wrote:
> > > > On Fri, Feb 11, 2005 at 11:10:15AM -0
Sven Willenberger <[EMAIL PROTECTED]> writes:
>> 2. _Or_ plperl does not go all the way to be a conformant perl-embedding
>> application. It looks at $Config{archlibexp}, but it does not follow
>> directions described in perlembed(1). In this case it's linking
>> should be fixed to respect that.
We have started
getting the error
FATAL: terminating connection due to administrator
command
in some of our
processes. Searching in the archives, I gather that this is caused by a SIGTERM,
and might be coming from a ulimit problem.
We are running
Solaris 9/Intel, and the ulimit for
Martijn van Oosterhout writes:
> Here's the function definition:
>> CREATE OR REPLACE FUNCTION "public"."sp_insert_users_new" (varchar, varch=
> ar,
>> varchar, varchar, smallint, date, smallint, smallint, varchar, varchar,
>> varchar, varchar, varchar, varchar, varchar) RETURNS integer AS
> As
My database table holds phone numbers that may contain characters other
than digits (that's not a problem in itself).
I want to be able to apply a regular expression (to ignore all
characters except digits) to the attribute 'phone' first and then for
the ILIKE to compare
the result to $telephon
On Fri, Feb 11, 2005 at 02:56:09PM -0600, Cristian Prieto wrote:
> Hello, I migrated a database from postgresql 7.4 to postgresql 8.0.1 in a
> Linux Fedora Core 3.
> The tables and objects in the database schema are working ok...
> I was just writing that function:
Here's the function definition:
On Fri, Feb 11, 2005 at 03:50:05PM +0100, Harald Fuchs wrote:
> In article <[EMAIL PROTECTED]>,
> Martijn van Oosterhout writes:
> > At the moment people are talking about parsing strings to get the
> > output. That output has the same issues as what's being proposed here,
> > we're just saving th
On Fri, 2005-02-11 at 21:35 +0100, Anton Berezin wrote:
> On Fri, Feb 11, 2005 at 11:10:15AM -0500, Sven Willenberger wrote:
> > On Fri, 2005-02-11 at 16:46 +0100, Palle Girgensohn wrote:
> > > --On fredag, februari 11, 2005 10.24.22 -0500 Sven Willenberger
> > > <[EMAIL PROTECTED]> wrote:
>
> >
On Fri, Feb 11, 2005 at 04:26:04PM -0500, Alex Turner wrote:
>
> What does the column 'relation' in pg_locks key to (Is there any docs
> on the website for this?)
See the "System Catalogs" chapter in the documentation (substitute
your version of PostgreSQL in the link):
http://www.postgresql.org/
On Fri, Feb 11, 2005 at 04:24:17PM -0500, Alex Turner wrote:
>
> pg_locks - awesome - I will check it out...
See also pg_stat_activity. If you don't see anything in the
current_query column then edit postgresql.conf and set
stats_command_string = true, then restart the database. With
this config
pg_locks - awesome - I will check it out...
I think it's uncommitted transactions that are causing the problem.
The original code was written very transactionaly.
Alex Turner
netEconomist
On Fri, 11 Feb 2005 14:20:32 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Fri, Feb 11, 2005 at 04:0
What does the column 'relation' in pg_locks key to (Is there any docs
on the website for this?)
Alex
On Fri, 11 Feb 2005 14:20:32 -0700, Michael Fuhr <[EMAIL PROTECTED]> wrote:
> On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote:
> >
> > I am now having trouble with psycopg locking up
On Fri, Feb 11, 2005 at 04:05:03PM -0500, Alex Turner wrote:
>
> I am now having trouble with psycopg locking up my database somehow.
> It's almost like there are outstanding locks on objects in the database
> that are preventing other threads operating.
Do you mean the Python script itself is loc
I am getting an error from pygresql:
Exception pg.InternalError: 'Connection already closed' in > ignored
I have already mailed the pysgresql list but without much luck. I'm
hoping somebody here might be able to help me figure out whats going
on with this.
I have tried switching to psycopg, but
Funny you should say that - the system I am developing has a similar
system, although not as fully developed. I am now having trouble with
psycopg locking up my database somehow. It's almost like there are
outstanding locks on objects in the database that are preventing other
threads operating.
Hello, I migrated a database from postgresql 7.4 to postgresql 8.0.1 in a
Linux Fedora Core 3.
The tables and objects in the database schema are working ok...
I was just writing that function:
--- Begin of my function ---
CREATE OR REPLACE FUNCTION "public"."sp_insert_users_new" (varchar, varchar,
In article <[EMAIL PROTECTED]>,
Martijn van Oosterhout writes:
> To be honest, I'm not sure this a real problem. You could simply label
> the first columns a rownumber and a depth number.
[See below ]
> At the moment people are talking about parsing strings to get the
> output. That output has
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Fri, Feb 11, 2005 at 02:32:31PM -0500, Tom Lane wrote:
>> There are some limited cases you could handle in plpgsql using the
>> polymorphic-functions stuff (ie, ANYELEMENT not ANY) but it still has
>> no concept of a run-time type test.
> Eh? What am
On Fri, Feb 11, 2005 at 02:32:31PM -0500, Tom Lane wrote:
> There are some limited cases you could handle in plpgsql using the
> polymorphic-functions stuff (ie, ANYELEMENT not ANY) but it still has
> no concept of a run-time type test.
Eh? What am I misunderstanding then? The following done in
Thanks Tom.
I didn't know I needed a RETURN statement at the end. I assumed that the
"return next" statement would be enough. It works fine now.
---
Ignacio Colmenero
Software Development
Micotan Software Company Ltd.
-Original Message-
From: [E
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Fri, Feb 11, 2005 at 08:40:53PM +, NosyMan wrote:
>> I want something like this:
>>
>> CREATE OR REPLACE FUNCTION myfunction(_param ANY) RETURNS INTEGER AS $$
> PostgreSQL has an undocumented IS OF construct:
... which won't help since IS OF is a
"Ignacio Colmenero" <[EMAIL PROTECTED]> writes:
> I get the error: ERROR: control reached end of function without RETURN
> What can be wrong?
You don't have a RETURN statement.
regards, tom lane
---(end of broadcast)---
TI
On Fri, Feb 11, 2005 at 08:40:53PM +, NosyMan wrote:
>
> How can I test the type of a parameter passed to a function via ANY data
> type?
> I want something like this:
>
> CREATE OR REPLACE FUNCTION myfunction(_param ANY) RETURNS INTEGER AS $$
> BEGIN
> IF "_param IS OF
Hi all.
I created a function, using EMS:
CREATE OR REPLACE
FUNCTION "geo_schema"."search_geo" () RETURNS SETOF varchar
AS
$body$
declare x_longlat
cursor for select long_lat from h2s ;
declare
var_longlat public.geometry ;
declare x_id
varchar ;
begin
open x_longlat ;
<>
Mark Harrison <[EMAIL PROTECTED]> writes:
> I'd like to present some statistics on our database tables,
> showing the number of rows and approximate amount of data
> in bytes, e.g. something like this from one of our other
> databases:
> tasks (546916 Kb,62018 rows)
> Exact numbers don't matter,
On Fri, Feb 11, 2005 at 10:20:47AM -0800, Mark Harrison wrote:
> I'd like to present some statistics on our database tables,
> showing the number of rows and approximate amount of data
> in bytes, e.g. something like this from one of our other
> databases:
>
> tasks (546916 Kb,62018 rows)
Take
Hi there,
How can I test the type of a parameter passed to a function via ANY data type?
I want something like this:
CREATE OR REPLACE FUNCTION myfunction(_param ANY) RETURNS INTEGER AS $$
BEGIN
IF "_param IS OF INTEGER TYPE" THEN
-- do something w
Bruno Almeida do Lago wrote:
> Hi,
>
>
>
> I'd like to know if there is something like "insert /*+ append */ into"
> and materialized views (from Oracle) on PostgreSQL.
We have SELECT INTO if that helps.
You can do materialized views and use triggers or rules to keep the
materialized view curr
I'd like to present some statistics on our database tables,
showing the number of rows and approximate amount of data
in bytes, e.g. something like this from one of our other
databases:
tasks (546916 Kb,62018 rows)
Exact numbers don't matter, I just want to present a feel
for the amount of data we
On Friday 11 February 2005 13:39, Bruno Wolff III wrote:
> On Fri, Feb 11, 2005 at 11:07:24 +,
>
> David Goodenough <[EMAIL PROTECTED]> wrote:
> > I thought of using an inner select for the join, and using limit 1 to
> > get just the one, and forcing the order by to give me the billing
> > ad
On Friday 11 February 2005 11:41, Janning Vygen wrote:
> Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough:
> > I have an address table, with all the normal fields and a customer name
> > field and an address type. There is a constraint that means that the
> > combination of customer and
On Friday 11 February 2005 11:46, Richard Huxton wrote:
> David Goodenough wrote:
> > I realise this is not strictly a Postgreslql question, but if the best
> > way to solve it involves using PG extensions, such as the PG procedural
> > languages I am only going to do this on PG and so I am happy t
On Friday 11 February 2005 11:31, Matt K wrote:
> David Goodenough wrote:
> >I could do this by doing a select * from addresses where customer = ?
> >and type = 'billing', looking to see if there is a result row and if not
> >repeating the query with type = 'default', but that seems inelegant to
>
To log all queries - in your postgresql.conf file, set the following:
log_statement = true
Mark wrote:
Hi,
I'm getting some errors in log file saying "invalid character at
position
#20..." I know that this is most likely that query is wrong.
Is it possible to capture all queries that get send or at
M> Hi,
M> I'm getting some errors in log file saying "invalid character at
M> position
M> #20..." I know that this is most likely that query is wrong.
M> Is it possible to capture all queries that get send or at least the
M> invalid queries?
M> I'm using postgresql 7.4.3 on Red Hat 9
M> Thanks,
Thanks Tom, I figured it out after I saw the error message when trying
to vacuum template0.
I think the 7.4.x docs could do with a more explanation.
The 8.0 docs are better although a more explicit explanation stating
that you MUST vacuum analyze template1 (periodically) would be useful.
Thanks
John Sidney-Woollett <[EMAIL PROTECTED]> writes:
> However the implication (and my confusion) is that you need to vacuum
> your own databases only. It's not clear (to me) that you have to do the
> same for template0 and template1 as well.
You have to vacuum template1, but not template0 because t
Hi,
I'm getting some errors in log file saying "invalid character at
position
#20..." I know that this is most likely that query is wrong.
Is it possible to capture all queries that get send or at least the
invalid queries?
I'm using postgresql 7.4.3 on Red Hat 9
Thanks,
Mark
<[EMAIL PROTECTED]> wrote in message
news:[EMAIL PROTECTED]
> Hi,
>
> How do I call a StoredProcdure, written in PlPqSql
> which returns a set of records (or tableWhatever)
> and wants parameters,
> from ADO while using "adCmdStoredProc" ?
I don't believe that works (anyone who thinks I'm wrong,
Terry Lee Tucker wrote:
On Friday 11 February 2005 07:47 am, Shridhar Daithankar saith:
No. PostgreSQL backend process is single threaded. However each connection
gets a separate backend process. Hence on SMP machines, all available CPUs
can potentially be used for multiple connections.
Regards,
Sh
On Thursday 10 February 2005 08:56 pm, Alex Turner wrote:
> Just a small warning for those people using python with postgresql:
> pysgresql and psycopg are very different animals. You cannot drop in
> one as a replacement for the other, even though both 'claim' to be DB
> API 2.0 compliant.
None
Tom Lane <[EMAIL PROTECTED]> writes:
> Well, the point is that there are potentially three types of statements
> involved:
>
> 1. SELECTs
> 2. Utility statements that can return tuples (EXPLAIN, SHOW, etc)
> 3. Utility statements that can't return tuples (ALTER, etc)
I'm not s
On Thu, Feb 10, 2005 at 10:30:26PM -0500, Tom Lane wrote:
> Well, the point is that there are potentially three types of statements
> involved:
>
> 1. SELECTs
> 2. Utility statements that can return tuples (EXPLAIN, SHOW, etc)
> 3. Utility statements that can't return tuples (ALT
Please ignore my previous post, if you still can... :-) I simply and
embarassingly forgot to commit the view to the db ooops.
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
On Thu, Feb 10, 2005 at 07:34:07PM -0500, Tom Lane wrote:
> Martijn van Oosterhout writes:
> > I've been wondering about that. A while ago the change was made from
> > outputting a NOTICE with the EXPLAIN output to returning a resultset.
> > If you could agree on what columns to return it might no
On Friday 11 February 2005 07:47 am, Shridhar Daithankar saith:
>
> No. PostgreSQL backend process is single threaded. However each connection
> gets a separate backend process. Hence on SMP machines, all available CPUs
> can potentially be used for multiple connections.
>
> Regards,
> Shridhar
I'm still quite new to postgres, struggeling with 'views'... I'm on
OS X, using aquadatastudio to interface the db.
Now, after issuing a 'CREATE VIEW' command, I get back a '1 record
affected' message, and I can use the view. However, the view doesn't
appear in the tree view of aquadatastudio (
On Fri, Feb 11, 2005 at 11:07:24 +,
David Goodenough <[EMAIL PROTECTED]> wrote:
>
> I thought of using an inner select for the join, and using limit 1 to
> get just the one, and forcing the order by to give me the billing
> address by preference, but I am then dependant on the sort order
>
On Feb 11, 2005, at 7:47 AM, Shridhar Daithankar wrote:
On Friday 11 Feb 2005 6:01 pm, Surabhi Ahuja wrote:
does postgres support notifications.
for eg. through one connection to the server, i begin a transaction
and i
do inserts there. in another sessions, i want to get notified that
inserts
ar
Surabhi Ahuja wrote:
does postgres support notifications.
for eg. through one connection to the server, i begin a transaction and i do inserts there.
in another sessions, i want to get notified that inserts are taking place in someother session.
please help.
You might want to look in the manual
On Friday 11 Feb 2005 6:01 pm, Surabhi Ahuja wrote:
> does postgres support notifications.
>
> for eg. through one connection to the server, i begin a transaction and i
> do inserts there. in another sessions, i want to get notified that inserts
> are taking place in someother session. please help
does postgres support notifications.
for eg. through one connection to the server, i
begin a transaction and i do inserts there.
in another sessions, i want to get notified that
inserts are taking place in someother session.
please help.
a few other questions
Q1. is there anyway by which
David Goodenough wrote:
I realise this is not strictly a Postgreslql question, but if the best way to
solve it involves using PG extensions, such as the PG procedural languages
I am only going to do this on PG and so I am happy to use them.
I have an address table, with all the normal fields and a
Am Freitag, 11. Februar 2005 12:07 schrieb David Goodenough:
>
> I have an address table, with all the normal fields and a customer name
> field and an address type. There is a constraint that means that the
> combination of customer and type have to be unique. Normally the
> only record per cust
David Goodenough wrote:
I could do this by doing a select * from addresses where customer = ?
and type = 'billing', looking to see if there is a result row and if not
repeating the query with type = 'default', but that seems inelegant to
me.
Use NULL to indicate that the customer type is defa
(Apologies if this message comes twice - my imap server and mail client
had a little problem)...
I've just noticed that the v8 docs are MUCH better in explaining this
than the 7.4.6 docs that I'm using (since I'm using 7.4.6 in production).
Perhaps if the same texts
play=# VACUUM;
WARNING: so
I realise this is not strictly a Postgreslql question, but if the best way to
solve it involves using PG extensions, such as the PG procedural languages
I am only going to do this on PG and so I am happy to use them.
I have an address table, with all the normal fields and a customer name
field and
I've just noticed that the v8 docs are MUCH better in explaining this
than the 7.4.6 docs that I'm using (since I'm using 7.4.6 in production).
Perhaps if the same texts
play=# VACUUM;
WARNING: some databases have not been vacuumed in 1613770184 transactions
HINT: Better vacuum them within 533
Hi list,
I am working with Postgres 8.0.0 final on a SuSE Linux 9.2 box. I tried
dropdb ax20050206
WARNING: could not remove database directory
"/export/home/postgres/data2/base/115101837"
DROP DATABASE
(maybe there was still some vacuum activity on the database...)
a) There was a posting in
ht
Thanks Richard, I found the page too...
However the implication (and my confusion) is that you need to vacuum
your own databases only. It's not clear (to me) that you have to do the
same for template0 and template1 as well.
Perhaps when someone is updating the docs, something more explicit than
Hi,
I’d like to know if there is something like “insert
/*+ append */ into” and materialized views (from Oracle) on PostgreSQL.
How can I pass hints to postgre and what hints are available?
Regards,
Bruno
John Sidney-Woollett wrote:
Ah.., no we're not vacuuming template0 or 1.
I didn't realise that the transaction ID would be stored here - I
assumed that they'd be in our database.
Do I need to need to (plain) vacuum, or vacuum full these template0 and
template1? And is this something that can be
Ah.., no we're not vacuuming template0 or 1.
I didn't realise that the transaction ID would be stored here - I
assumed that they'd be in our database.
Do I need to need to (plain) vacuum, or vacuum full these template0 and
template1? And is this something that can be done once a week rather
tha
>>Von: Duffner
> Von: Shachar Shemesh
> Betreff: Re: [GENERAL] ADO adCmdStoredProc PlPgSql-SP Parameters
> >How do I call a StoredProcdure, written in PlPqSql
> >which returns a set of records (or tableWhatever)
> >and wants parameters,
> >from ADO while using "adCmdStoredProc" ?
> Which driver a
John Sidney-Woollett wrote:
I've got a 7.4.6 db running on linux where we've been vacuuming "full"
the database each night, and have recently switched to "vacuum analyse"
instead.
We keep seeing this message at the end of the vacuum run:
WARNING: some databases have not been vacuumed in 2013308
85 matches
Mail list logo
