As I was not using any of those duplicate columns, * was easier to use
and I did not think about trying to use the other ones.
In fact when you do try to use one of those columns in the query, it
doesn't allow the query because of ambiguous columns.
Thank you for fixing this particular problem, ev
# CREATE OR REPLACE FUNCTION get_default_value (text, text, text) RETURNS text AS '
# SELECT adsrc
# FROM pg_attrdef, pg_class, pg_namespace, pg_attribute
# WHERE
# adrelid = pg_class.oid AND
# pg_class.relnamespace = pg_namespace.oid AND
# pg_attribute.attnum = pg_attrdef.adnum A
On Wed, Oct 27, 2004 at 09:06:15PM -0700, Robby Russell wrote:
>
> Ok, so how would I go about getting the sequence name for a SERIAL
> field on any given schema.table? I would like to build a function
> that would return this value if I pass it the schema and table (and
> fieldname is necessary)
# I figured out how to get this:
#
# foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
# pg_class WHERE relname = 'foo');
#adsrc
#
# nextval('public.foo_id_seq'::text)
# (1 row)
#
# However, this will break as soon as I do th
Robby Russell <[EMAIL PROTECTED]> writes:
> Ok, so how would I go about getting the sequence name for a SERIAL
> field on any given schema.table?
8.0 will have a function pg_get_serial_sequence to do this for you.
If you can't wait, the secret is to look in pg_depend for the dependency
link from t
On Wed, Oct 27, 2004 at 09:33:21PM -0700, Robby Russell wrote:
> So, it was a nice attempt, but I am back to the need to of determining
> the sequence name using a schema and a table.
The schema of a table is stored in pg_class.relnamespace, which is an
Oid of the pg_namespace catalog. With that
Joel <[EMAIL PROTECTED]> writes:
> Any thoughts on the urgency of the move?
How large is your pg_log file? 7.1 was the last release that had the
transaction ID wraparound limitation (after 4G transactions your
database fails...). If pg_log is approaching a gig, you had better
do something PDQ.
On Wed, 2004-10-27 at 21:33 -0700, Robby Russell wrote:
>
> I figured out how to get this:
>
> foo=> SELECT adsrc FROM pg_attrdef WHERE adrelid = (SELECT oid FROM
> pg_class WHERE relname = 'foo');
>adsrc
>
> nextval('public.foo_id_seq'::text)
On Wed, 2004-10-27 at 21:06 -0700, Robby Russell wrote:
> I am trying to track down a method of determining what a sequence name
> is for a SERIAL is in postgresql.
>
> For example,
>
> CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);
>
> \d foo
>Table "pu
I am trying to track down a method of determining what a sequence name
is for a SERIAL is in postgresql.
For example,
CREATE TABLE foo (id SERIAL PRIMARY KEY NOT NULL, bar TEXT);
\d foo
Table "public.foo"
Column | Type | Modifiers
+
On Tue, 26 Oct 2004 13:30:49 +0200
Ian Barwick <[EMAIL PROTECTED]> wrote
> On Tue, 26 Oct 2004 18:22:55 +0900, Joel <[EMAIL PROTECTED]> wrote:
> > I seem to remember reading a post on this, but searching marc does not
> > seem to bring it up immediately.
> >
> > Company BBS is on postgresql, but
Mariusz =?iso-8859-2?q?Czu=B3ada?= <[EMAIL PROTECTED]> writes:
> My question: is it possible to extend (in near future) CREATE SCHEMA syntax
> with feature like:
> CREATE SCHEMA user_xxx_schema FROM default_user_schema [WITH CONTENT];
> ?
> It should create *every* object existing in 'defaul
Hi,
In a database I have to create new schemas with exactely the same structure as
the default one. Of course, I could reverse a schema with pg_dump, then apply
the script to the newly created one. The problem is the base schema sometimes
changes so I should generate scripts every time. Also I
On Wednesday October 27 2004 5:34, Ed L. wrote:
> On Wednesday October 27 2004 5:24, Tom Lane wrote:
> > An example of what you won't be able to do:
> >
> > regression=# select distinct * from fooview;
> > ERROR: failed to find conversion function from "unknown" to text
>
> Is that 8.0 you're work
On Wednesday October 27 2004 5:24, Tom Lane wrote:
> An example of what you won't be able to do:
>
> regression=# select distinct * from fooview;
> ERROR: failed to find conversion function from "unknown" to text
Is that 8.0 you're working against there? Here's my 7.4.6 installation:
$ psql -c
"Ed L." <[EMAIL PROTECTED]> writes:
> On 7.4.6, is there any problem with defining one column of a view to be a
> string literal? For example ...
> $ psql -c "create view fooview as select 'bar' as footype"
> WARNING: column "footype" has type "unknown"
> DETAIL: Proceeding with relation creat
On 7.4.6, is there any problem with defining one column of a view to be a
string literal? For example ...
$ psql -c "create view fooview as select 'bar' as footype"
WARNING: column "footype" has type "unknown"
DETAIL: Proceeding with relation creation anyway.
CREATE VIEW
Or is this warning j
Thomas Hallgren <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Right. Depending on your OS you may be able to catch a signal that
>> would kill a thread and keep it from killing the whole process, but
>> this still leaves you with a process memory space that may or may not
>> be corrupted.
> It
On Thu, Oct 28, 2004 at 04:08:53AM +0600, Denis Zaitsev wrote:
> How can I calculate the number of second in some interval? Neither
> the secods part, nor the seconds after midnight... Just the full
> quantity of the seconds. I haven't find any function.
test=> SELECT extract(epoch FROM '5 hour
Tom Lane wrote:
Right. Depending on your OS you may be able to catch a signal that
would kill a thread and keep it from killing the whole process, but
this still leaves you with a process memory space that may or may not
be corrupted. Continuing in that situation is not cool, at least not
accordi
On Thu, 2004-10-28 at 04:08 +0600, Denis Zaitsev wrote:
> How can I calculate the number of second in some interval? Neither
> the secods part, nor the seconds after midnight... Just the full
> quantity of the seconds. I haven't find any function.
>
> Thanks in advance.
>
SELECT extract(epoch
Terry Lee Tucker <[EMAIL PROTECTED]> writes:
> I would like to be able to provide feedback to the user when they
> select a row for update (using SELECT FOR UPDATE). At present, if the
> row is being accessed (with SELECT FOR UPDATE) by another user, the
> application just sits there waiting.
To m
On Thu, Oct 28, 2004 at 04:08:53AM +0600, Denis Zaitsev wrote:
> How can I calculate the number of second in some interval? Neither
> the secods part, nor the seconds after midnight... Just the full
> quantity of the seconds. I haven't find any function.
I think you can do that using
EXTRACT(e
Tom Lane wrote:
Bruno Wolff III <[EMAIL PROTECTED]> writes:
Wikipedia gives 365.242189670 days (86400 seconds) as the length of
the mean solar year in 2000. To give you some idea of how constant
that values is, Wikipedia claims that 2000 years ago the mean solar
year was about 10 seconds longer.
On Wed, Oct 27, 2004 at 06:30:24PM -0400, Tom Lane wrote:
> Denis Zaitsev <[EMAIL PROTECTED]> writes:
> > How can I calculate the number of second in some interval? Neither
> > the secods part, nor the seconds after midnight... Just the full
> > quantity of the seconds. I haven't find any functi
Denis Zaitsev <[EMAIL PROTECTED]> writes:
> How can I calculate the number of second in some interval? Neither
> the secods part, nor the seconds after midnight... Just the full
> quantity of the seconds. I haven't find any function.
EXTRACT(EPOCH FROM interval_value)
r
Tom Lane wrote:
Doug McNaught <[EMAIL PROTECTED]> writes:
template1=# select '1 year'::interval = '360 days'::interval;
?column?
--
t
(1 row)
Yeah, if you look at interval_cmp_internal() it's fairly obvious why.
I think that this definition is probably bogus, and that only interval
On Thu, Oct 28, 2004 at 04:08:53AM +0600, Denis Zaitsev wrote:
> How can I calculate the number of second in some interval? Neither
> the secods part, nor the seconds after midnight... Just the full
> quantity of the seconds. I haven't find any function.
>
> Thanks in advance.
Oh, I'm sorry.
Martijn van Oosterhout wrote:
A lot of these advantages are due to sharing an address space, right?
Well, the processes in PostgreSQL share address space, just not *all*
of it. They communicate via this shared memory.
Whitch is a different beast altogether. The inter-process mutex handling
that yo
How can I calculate the number of second in some interval? Neither
the secods part, nor the seconds after midnight... Just the full
quantity of the seconds. I haven't find any function.
Thanks in advance.
---(end of broadcast)---
TIP 7: don't for
Bruno Wolff III <[EMAIL PROTECTED]> writes:
> On Wed, Oct 27, 2004 at 22:10:05 +0200,
>> 2. Let's change so that "add_missing_from" is disabled by default and
>> doesn't affect the DELETE statement at all.
> That is supposed to happen. My memory was that 8.0 was the release that
> the default was
Greetings:
I am working on converting a transportation application from a Progress
database to PostgreSQL. This application will be hammered by about 75 users
at any given time. Also, depending on the operation, there are many record
updates that occur at the trigger level. I would like to be a
On Wed, Oct 27, 2004 at 22:10:05 +0200,
> 2. Let's change so that "add_missing_from" is disabled by default and
> doesn't affect the DELETE statement at all.
That is supposed to happen. My memory was that 8.0 was the release that
the default was going to change, but if not then it should be 8.1.
Martijn van Oosterhout <[EMAIL PROTECTED]> writes:
> ... Signals are shared between threads. Now, you could ofcourse catch
> these signals but you only have one address space shared between all
> the threads, so if you want to exit to get a new process image (because
> something is corrupted), you
On Wed, Oct 27, 2004 at 10:07:48PM +0200, Thomas Hallgren wrote:
> >Threaded servers have one main advantate:
> >Threads are lightweight processes and starting a new thread is faster
> >than starting a new executable.
> >
> A few more from the top of my head:
A lot of these advantages are due to s
Martijn,
I realize that the change I'm proposing might be too complex to be added
in the upcoming 8.0 release. I do find this discussion interesting
though, so please bear with me while I try to tie up some loose ends.
UPDATE [ ONLY ] table SET col = expression [, ...]
[ FROM fromlist ]
[ WHE
On Wed, Oct 27, 2004 at 05:56:16PM +0200, [EMAIL PROTECTED] wrote:
>
> I understand PostgreSQL uses processes rather than threads. I found this
> statement in the archives:
>
> "The developers agree that multiple processes provide
> more benefits (mostly in stability and robustness) than costs (m
Dann,
I'm not advocating a multi-threaded PostgreSQL server (been there, done
that :-). But I still must come to the defense of multi-threaded systems
in general.
You try to convince us that a single threaded system is better because
it is more tolerant to buggy code. That argument is valid and
Le mer 27/10/2004 à 11:59, Pierre-Frédéric Caillaud a écrit :
>
>
> You can't express it directly with a CHECK constraint but you can do this
> :
>
> - add CHECK( test_array( yourcolumn )) in your table definition
> - create function test_array which takes an array and looks
* Eric <[EMAIL PROTECTED]> [2004-10-27 14:14:25 -0400]:
> Is there something to interface postgreSQL with QMail to store mails
> in pgsql instead of using mbox or maildir?
This looks informative:
http://qmail-sql.digibel.be/
--
Steven Klassen - Lead Programmer
Command Prompt, Inc. - http://www
Is there something to interface postgreSQL with QMail to store mails in
pgsql instead of using mbox or maildir?
Or maybe it's not a good idea to do that?
I think there is some adavantages...
---(end of broadcast)---
TIP 3: if posting/reading thro
> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] On Behalf Of
> Thomas Hallgren
> Sent: Wednesday, October 27, 2004 11:16 AM
> To: [EMAIL PROTECTED]
> Subject: Re: [GENERAL] Reasoning behind process instead of
> thread based
>
>
> [EMAIL PROTECTED] wrote:
> >>
Sim Zacks <[EMAIL PROTECTED]> writes:
> /*Here is the virtual table I mentioned using select * on a join*/
> (select * from PackagePricingGroups b Inner JOIN PricingGroups c ON b.PricingGroupID
> = c.PricingGroupID) groups
Okay, evidently the problem is that you have identically named
columns in
On Tue, Oct 26, 2004 at 03:10:04PM -0400, Brian Maguire wrote:
> Thanks. We do have it set to 15 mb. I would think that 16 mb would not
> make a big difference. Do you have any other ideas?
Huh? No, you have it set to 15 *segments*, each of which is 16 MB long.
Maybe setting it higher will h
[EMAIL PROTECTED] wrote:
Two: If a
single process in a multi-process application crashes, that process
alone dies. The buffer is flushed, and all the other child processes
continue happily along. In a multi-threaded environment, when one
thread dies, they all die.
So this means that if a single
On Wed, Oct 27, 2004 at 07:47:03PM +0200, [EMAIL PROTECTED] wrote:
> >Two: If a
> > single process in a multi-process application crashes, that process
> > alone dies. The buffer is flushed, and all the other child processes
> > continue happily along. In a multi-threaded environment, when one
>
On Tue, Oct 26, 2004 at 08:24:56PM -0500, Naeem Bari wrote:
> The only way this works in postgres is by casting 'GREEN' to text using
> 'GREEN'::text
>
> The problem is then this does not work with oracle.
So use a standards-conformant cast, like
cast('GREEN' as text)
--
Alvaro Herrera ()
"T
On Wed, Oct 27, 2004 at 05:59:46PM +0200, Pierre-Fr?d?ric Caillaud wrote:
>
> - add CHECK( test_array( yourcolumn )) in your table definition
> - create function test_array which takes an array and looks if all
> its elements are in your table T2, I do something like comparing
On Tue, Oct 26, 2004 at 11:44:42AM -0400, Brian Maguire wrote:
> Can too many btree indexes cause page level locking?
Yes, too many btree indexes can, as can a single btree index.
> I am experiencing locking related on two tables. Each has several
> indexes on it (4 or more). One table is fre
>Two: If a
> single process in a multi-process application crashes, that process
> alone dies. The buffer is flushed, and all the other child processes
> continue happily along. In a multi-threaded environment, when one
> thread dies, they all die.
So this means that if a single connection thr
On some operating systems, like Windows and Solaris, processes are
expensive, while threads are cheap, so to speak. this is not the case
in Linux or BSD, where the differences are much smaller, and the
multi-process design suffers no great disadvantage.
Even on Windows or Solaris you can use techn
hi,
I think it works for me. what version of postgres do you have? maybe
you just need to upgrade : )
test=# select cust_id, 'TEST' as test, cust_address from customers;
cust_id | test | cust_address
+--+--
11 | TEST | 200 Maple Lane
100
On Wed, 2004-10-27 at 09:56, [EMAIL PROTECTED] wrote:
> Hello!
>
> I have a couple of final ( I hope, for your sake ) questions regarding
> PostgreSQL.
>
> I understand PostgreSQL uses processes rather than threads. I found this
> statement in the archives:
>
> "The developers agree that multipl
[EMAIL PROTECTED] writes:
> "The developers agree that multiple processes provide
> more benefits (mostly in stability and robustness) than costs (more
> connection startup costs). The startup costs are easily overcome by
> using connection pooling.
> "
>
> Please explain why it is more stable and
You can't express it directly with a CHECK constraint but you can do this
:
- add CHECK( test_array( yourcolumn )) in your table definition
- create function test_array which takes an array and looks if all its
elements are in your table T2, I do something like comparing the length of
th
Try using EXECUTE.
http://www.postgresql.org/docs/7.4/static/plpgsql-
statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Oct 2
Psycopg and probably PySQL seem to have decided to return
a "float" type when libpq returns a string tagged with
the "numeric" property.
This can cause "pretty" printing problems when generating
reports. ( I like all of my decimal points to line up.)
For example in my python based browser if I try
Hello!
I have a couple of final ( I hope, for your sake ) questions regarding
PostgreSQL.
I understand PostgreSQL uses processes rather than threads. I found this
statement in the archives:
"The developers agree that multiple processes provide
more benefits (mostly in stability and robustness) t
Hi.
i have table like this:
create table my_data (
cond_1 int8,cond_2 varchar(),cond_3 cond_n whatrver ,data text)
This table represents a simple tree structure with known max level (n) .
This table is filled with data, but branches have not a same depth.
Now I need to select from table
sel
"Deepa K" <[EMAIL PROTECTED]> writes:
> I am running postgresql 7.1.3 in RedHat Linux 7.2.
You do realize that both your database and your OS are ancient versions
with many known bugs?
> NOTICE: RelationBuildDesc: can't open pg_trigger: Too many open files in
> system
I think this is pr
"Ben-Nes Michael" <[EMAIL PROTECTED]> writes:
> From time to time i get the following error:
> could not fork new process for connection: Resource temporarily unavailable
This generally means that the kernel has run out of memory.
> my conf is:
> max_connections = 400
Perhaps that is overly opti
Michael Glaesemann <[EMAIL PROTECTED]> writes:
> On Oct 27, 2004, at 6:00 PM, Martijn van Oosterhout wrote:
>> How can there be a "canonical list of known timezones" if every
>> operating system has it's own list. Maybe you can provide a base list,
>> but you have to allow for people to make their
Hi,
I am running postgresql 7.1.3 in RedHat Linux 7.2. From an
external C application, three connections are established with
postmaster (it
is started with -i option) through unix sockets. Two times I received
EPIPE error when trying to send a query to postmaster. This is because of
the ab
Hi everyone
>From time to time i get the following error:
could not fork new process for connection: Resource temporarily unavailable
im using Postgres 7.4.5, with kernel 2.6.2
my conf is:
max_connections = 400
and system is:
www3:/etc/postgresql# ulimit -a
core file size(blocks, -c)
Is there a way to define a foreign key for the values of an array?
For example, if table T1 is having a colum A which is defined as integer[] can I
define a foreign key in order to force each value to be a pointer (index) to a row in
a table T2?
If yes, how? Is there any shortcomings to this ap
Thanks for your answer!
I would like to kill only a postgres session not postmaster.
The table pg_session indicates the pid of the wrong session but kill -INT didn't
stop this session.
pg_ctl works fine when i want to stop postmaster with the argument '-m immediate stop'
Message d'origine
Thank you Michael. This should work
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Michael Glaesemann
Sent: Wednesday, October 27, 2004 9:27 AM
To: Carlos
Cc: '[EMAIL PROTECTED]'
Subject: Re: [GENERAL] Changing access permissions without re-starting the
On Wed, Oct 27, 2004 at 00:10:27 +0200,
Dawid Kuroczko <[EMAIL PROTECTED]> wrote:
> 3. If you'll need things like "last 50 keys", you can SELECT * FROM
> foo ORDER BY yourserialkey DESC LIMIT 50;
You really shouldn't be doing that if you are using sequences to generate
the key. Sequences are jus
On Oct 27, 2004, at 9:23 PM, Carlos wrote:
PostgreSQL? I often want to preclude all IP address but one from
accessing the database. Currently, I change the pg_hba.conf file and
re-start the database but I would like to be able to do the same thing
without having to re-start.
Try pg_ctl reload
On Tue, Oct 26, 2004 at 08:27:31PM -0600, Scott Marlowe wrote:
> Were you running a later version, you'd have the option of logging your
> queries. I don't think 7.1 supported that though.
It certainly did. I can't remember the invocation. You'd better
have a log rotator, though, if you log all
Hi Forum,
Is there a way to change the database access permissions for
different IP addresses without having to re-start PostgreSQL? I often
want to preclude all IP address but one from accessing the database.
Currently, I change the pg_hba.conf file and re-start the database but I would
Henriksen, Jonas F wrote:
Hi,
is it possible to write a trigger, using pl/pgSQL, that updates
tables in a different database than the one the trigger is called
from? If it is, what is the syntax for calling the other database? My
to databases are on the same server.
Look into the dblink package in
> is it possible to write a trigger, using pl/pgSQL, that
> updates tables in a different database than the one the trigger
> is called from? If it is, what is the syntax for calling the
> other database? My to databases are on the same server.
One way I can think of is using dblink from the contri
Hi,
is it possible to write a trigger, using pl/pgSQL, that updates tables in a different
database than the one the trigger is called from? If it is, what is the syntax for
calling the other database? My to databases are on the same server.
Regards Jonas:))
---(end o
On Wed, 2004-10-27 at 09:00 +0200, Thomas Hallgren wrote:
> Using OID's is a good idea, but I think a canonical list of known
> timezone to OID mappings must be maintained and shipped with the
> PostgreSQL core.
>
> If OID's are generated at initdb time, there's a great risk that the
> OID's w
On Oct 27, 2004, at 6:00 PM, Martijn van Oosterhout wrote:
On Wed, Oct 27, 2004 at 09:21:39AM +0200, Thomas Hallgren wrote:
Using OID's is a good idea, but I think a canonical list of known
timezone to OID mappings must be maintained and shipped with the
PostgreSQL core.
How can there be a "canonic
[EMAIL PROTECTED] wrote:
Hello !
To kill a session i used KILL -INT .
This command wasn't successfull. The processus is still here when a
'ps ax' or a 'select * from pg_stat_activity'
Is there an other way to kill this process only because a web server
24/7 use postgres and cannot stop postgresql n
On Wed, Oct 27, 2004 at 09:21:39AM +0200, Thomas Hallgren wrote:
> Martijn,
> >I agree. One issue I can think of is that if you store each timestamp
> >as a (seconds,timezone) pair, the storage requirements will balloon,
> >since timezone can be something like "Australia/Sydney" and this will
> >b
I didn't see that join syntax in the documentation for delete, thanks
for pointing it out.
MS SQL Server syntax for a delete is a little less confusing, IMHO.
instead of DELETE FROM x WHERE x.a = table.a and x.b > table.b and table.c = 4;
they have DELETE x FROM x join table on x.a = table.a and
On Wed, Oct 27, 2004 at 12:15:10AM +0200, Thomas Hallgren wrote:
> Martijn,
> > Do you have a better
> >suggestion, other than forbidding the currently allowed syntax?
> >
> Yes I do.
>
> We agree that my second example should be disallowed since the semantics
> of the FROM clause is different fo
Sally Sally wrote:
This
existing unique field will have to be a character of fixed length
(VARCHAR(12)) because although it's a numeric value there will be
leading zeroes.
Plenty of people are contributing their tuppence-worth regarding the
choice of surrogate vs natural primary key.
Can I j
Mayra,
I need info on the caracteristics of object
relational databases and their advantages as well as disdvantages in
comparison to relational databases and OO Databases! Please explain
these chacteristics with respect to what Postgresql can and cannot do.
Thanks for your assistance.
With
Hello !
To kill a session i used KILL -INT .
This command wasn't successfull. The processus is still here when a 'ps ax' or a
'select * from pg_stat_activity'
Is there an other way to kill this process only because a web server 24/7 use postgres
and cannot stop postgresql now.
Thanks !
HM
P
OOps. Didn't send it to the list. (There has to be a better way of
doing this then always having to remember to change the recipient.)
I'm sorry, I thought I described the problem pretty clearly.
Here is the actual queries with comment annotations where the problem
occurred.
1) This is the query
Hi again,
Another mistake of mine ... :) The real problem was that I used a Windows
based archiver (PowerArchiver) to unzip the gz file which - for some reasons
??? - chunked the long lines at aproximately every 16K. Using gunzip the
problem oozed away. :)
Bye,
-- Csaba
-Original Message--
Martijn,
I agree. One issue I can think of is that if you store each timestamp
as a (seconds,timezone) pair, the storage requirements will balloon,
since timezone can be something like "Australia/Sydney" and this will
be repeated for every value in the table. I don't know how to deal
easily with th
86 matches
Mail list logo
