create table temp(a timestamptz);
insert into temp(a) select NULL; /* this passes */
insert into temp(a) select NULL union select NULL; /* fails:
ERROR: column a is of type timestamp with time zone but expression is
of type text
*/
I need to insert hundreds of thousands of rows, and insert
On Wed, Sep 27, 2006 at 12:14:44PM +0300, Peter wrote:
create table temp(a timestamptz);
insert into temp(a) select NULL; /* this passes */
insert into temp(a) select NULL union select NULL; /* fails:
ERROR: column a is of type timestamp with time zone but expression is
of type text
*/
Martijn van Oosterhout wrote:
On Wed, Sep 27, 2006 at 12:14:44PM +0300, Peter wrote:
create table temp(a timestamptz);
insert into temp(a) select NULL; /* this passes */
insert into temp(a) select NULL union select NULL; /* fails:
ERROR: column "a" is of type timestamp with time
Jim Nasby wrote:
[EMAIL PROTECTED] ~]$ psql bar
psql: FATAL: database bar does not exist
[EMAIL PROTECTED] ~]$ createdb bar
createdb: database creation failed: ERROR: could not create directory
base/65536: File exists
[EMAIL PROTECTED] ~]$ createdb bar
Could this be a timing issue? IE: are
On Wed, Sep 27, 2006 at 01:05:56PM +0300, Peter wrote:
Typecast eliminates the issue, you're right on that as well. However, my
problem is that those statements are dynamically generated on various
tables/columns, so typecasting would mean extracting target field type
and translating
[EMAIL PROTECTED] wrote:
On the one hand I like how the schema scripts fail when there is a
single problem with a DDL statement.
On the other hand sometimes it is a pain - especially to take out all
the 'drop sequence', 'drop table' etc commands when creating a new
database.
Until 8.2 is
i want to check in a trigger if certain columns are not left empty. The
columns i have to check are stored in another table. How do i do the
following
BEGIN
SELECT INTO col_record * FROM modules WHERE type_module_id =
NEW.type_module_id AND is_afsluit_kolom;
IF NOT FOUND THEN
[EMAIL PROTECTED] wrote:
On the one hand I like how the schema scripts fail when there is a
single problem with a DDL statement.
Your mail address bounces. Unfortunately my Trash is broken, so I can't
show you the error; I was a bit quick deleting it.
--
Alban Hertroys
[EMAIL PROTECTED]
Hi,
I have a strange problem.
I have 2 database, both created with:
CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE =
pg_default;
Running the queries below on the first database is OK and do what I expect.
If I create a backup of the first datase and restore that
Thanks Jim. I dowloaded the latest version of the JDBC drivers and installed them on the affected sites. So far, there were no error reported in the last12 hours. Hope it works!Thanks again.AsokJim Nasby [EMAIL PROTECTED] wrote: On Sep 26, 2006, at 3:26 PM, Asok Chattopadhyay wrote: My
Typecast eliminates the issue, you're right on that as well. However, my
problem is that those statements are dynamically generated on various
tables/columns, so typecasting would mean extracting target field type
and translating fieldtype code into SQL typename. Rather messy.
On Sep 27, 2006, at 20:39 , Peter wrote:
Using COPY avoids this issue ofcourse, because there's a direct
link to the table. Similarly, as of 8.2 it will be possible to do:
INSERT INTO table (blah) VALUES (x,y,a),(f,d,g),(s,f,g), etc...
Which will also avoid the issue.
COPY wont work... my
On Wed, Sep 27, 2006 at 02:39:13PM +0300, Peter wrote:
COPY wont work... my list of columns for insert is also dynamically
built and will never cover all fields in table.
You don't have to include all columns for copy, just the fields you
copy into, the rest should get the default.
COPY table
On Sep 26, 2006, at 11:14 PM, Jorge Godoy wrote:
I dunno if this is the best list to ask about it, but it sounded
general
enough to me :-) Sorry if I'm on the wrong place.
I'd like to know how you're documenting your functions and stored
procedures,
including their usage, input and output
On Sep 27, 2006, at 6:24 AM, Jon Lapham wrote:
Jim Nasby wrote:
[EMAIL PROTECTED] ~]$ psql bar
psql: FATAL: database bar does not exist
[EMAIL PROTECTED] ~]$ createdb bar
createdb: database creation failed: ERROR: could not create
directory base/65536: File exists
[EMAIL PROTECTED] ~]$
On Sep 27, 2006, at 6:16 AM, jef peeraer wrote:
i want to check in a trigger if certain columns are not left empty.
The columns i have to check are stored in another table. How do i
do the following
BEGIN
SELECT INTO col_record * FROM modules WHERE type_module_id =
NEW.type_module_id AND
Hi,
I fell into the following problem (unfortunately, the database contents
has sensitive customer information, so can publish very little of that).
Currently postgress process takes close to 100% CPU time.
I've restarted the process a moment ago, and it was calm for a brief
minute.
It started
Hi Joost. (hoe gaat het ermee?)
I would like to test this too. Could you please provide the data you are
inserting into the database.
Regards,
Gevik
On Wed, 2006-09-27 at 13:13 +0200, Joost Kraaijeveld wrote:
Hi,
I have a strange problem.
I have 2 database, both created with:
CREATE
On Sep 26, 2006, at 3:25 PM, J S B wrote:
Hi,
I'm trying to make my database a client for an external unix based
deamon process acting as a server.
I was thinking of writing some clinet application in a shared
object in the database (same as what we do with socket programing)
that does
I havet to develop an search engine over a postgres table. I know in mysql the fulltext index to do it more quicky than using like % ...There is any like t in postgres?Thanks
Joost Kraaijeveld [EMAIL PROTECTED] writes:
I have 2 database, both created with:
CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE =
pg_default;
But have they got the same locale settings (lc_collate, lc_ctype)?
regards, tom lane
All,
I have a need to create/copy/delete files on my server from a function
in PostreSql. I am running 8.x PostreSql on Windows 200x and my
functions etc are currently only in PL/pgSQL. Is there a way to create
files/copy them and delete them from PL/pgSQL or could i call an
external bat
Title: Nachricht
Hi,
try
contrib/tsearch2
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/
HTH
Hakan
-Original Message-From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
On Behalf Of HHDirecto.NetSent: Wednesday, September 27,
2006 4:56 PMTo:
On Wed, Sep 27, 2006 at 11:15:19AM -0400, Oisin Glynn wrote:
All,
I have a need to create/copy/delete files on my server from a function
in PostreSql. I am running 8.x PostreSql on Windows 200x and my
functions etc are currently only in PL/pgSQL. Is there a way to create
files/copy them
Hi Tom,
On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote:
Joost Kraaijeveld [EMAIL PROTECTED] writes:
I have 2 database, both created with:
CREATE DATABASE muntdev WITH OWNER = postgres ENCODING = 'UTF8' TABLESPACE
= pg_default;
But have they got the same locale settings (lc_collate,
Joost Kraaijeveld [EMAIL PROTECTED] writes:
On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote:
But have they got the same locale settings (lc_collate, lc_ctype)?
According to the postgresql.conf of the *working* database the locales
are:
lc_messages = 'C'
lc_monetary = 'C'
On Wed, 2006-09-27 at 12:10 -0400, Tom Lane wrote:
Joost Kraaijeveld [EMAIL PROTECTED] writes:
On Wed, 2006-09-27 at 11:00 -0400, Tom Lane wrote:
But have they got the same locale settings (lc_collate, lc_ctype)?
According to the postgresql.conf of the *working* database the locales
On Sep 27, 2006, at 7:28 AM, Rafal Pietrak wrote:
Hi,
I fell into the following problem (unfortunately, the database
contents
has sensitive customer information, so can publish very little of
that).
Currently postgress process takes close to 100% CPU time.
I've restarted the process a
Jim Nasby wrote:
If I'm understanding what you're trying to do (have a function connect
to an external process?), I don't think anything exists right now. But
it shouldn't be too hard to write something to do that. You might want
to create a generic utility and put it on pgFoundry in case
Alban Hertroys wrote:
SELECT s.sid FROM stud s, stud_vera v WHERE s.sid = v.sid AND v.veraid =
34 AND s.sid NOT IN ( SELECT sid FROM stud_vera WHERE veraid = 2 );
I'm pretty sure it's not a deadlock. It probably takes very long for
some reason; maybe an explain of that query will give some
Joe Conway wrote:
Martijn van Oosterhout wrote:
It probably has something to with the fact that you didn't explain what
you meant by deadlock. Also, you refer to a temp table, yet don't
indicate which table it is.
Deadlock means it hangs up and doesn't terminate through timeout.
The system
Tom Lane wrote:
Kai Hessing [EMAIL PROTECTED] writes:
No one any idea? *sigh*
What makes you think it's a deadlock and not a very slow query? I'd be
checking if the tables were all ANALYZEd and comparing EXPLAIN output
to the old database ...
*hmm* the difference of 5 seconds on 8.0.8
Kai Hessing wrote:
Deadlock means it hangs up and doesn't terminate through timeout.
The system goes into an endless loop. The part ('SELECT sid FROM
stud_vera WHERE veraid = 2') seems to create a temporary table again and
again and again
It is possible for a query to run for many days,
On Wed, Sep 27, 2006 at 06:46:42PM +0200, Kai Hessing wrote:
Deadlock means it hangs up and doesn't terminate through timeout.
No, it doesn't. Deadlock means, for the two deadlocked queries, both
cannot possibly finish because each waits on a lock that the other
one holds.
You can cause
I'm using the the following scheduler script to create nightly backups in
Windows:
set pgpassword=mypass
set FILENAME=%DATE:~8,4%%DATE:~5,2%%DATE:~2,2%mybackup.backup
C:\Program Files\PostgreSQL\8.1\bin\pg_dump.exe -i -Z9 -b -v -f
%FILENAME% -F c -h localhost -U postgres mydb
I'm bit new to
I am using linux Red Hat 8 OS, it may be 2 old now, ^_^
I have just installed my opennms by rpm package, and now i am running
the installer to setup the PostgreSQL database:
# $OPENNMS_HOME/bin/install -disU
but some exception happens:
Michael Fuhr schreef:
On Thu, Sep 21, 2006 at 01:32:47AM -0700, Johan wrote:
Someone executed the query in psql for me and the problem seems to be
in a stored procedure triggered after update. This procedure complains
about the field.
Were you able to fix the problem or are you still
Am 26.9.2006 schrieb Tom Lane [EMAIL PROTECTED]:
Define stopped working ... what was wrong exactly?
oh, sorry. i forgot the error message:
Error: org.postgresql.util.PSQLException: ERROR: invalid reference to
FROM-clause entry for table t, SQL State: 42P01, Error Code: 0
(i use squirrel-sql)
Hi all,
I'm running a web application supported by postgresql 7.4 on an unix
system. The management part of application requires reindexing of
tables. I see that whenever reindexing runs with the while the table
is getting updated/inserted into, reindexing hangs. I understand this
is because
Sorry, I was a bit impatient and posted the same question in a newsgroup a few
days before. There is an answer now:
http://groups.google.de/group/comp.databases.postgresql/browse_thread/thread/36e5c65dd15b0388/1e5ff9b7e2c6863e?hl=de#1e5ff9b7e2c6863e
Of course, if anyone has an additional idea,
You can try using pg_pconnect instead of
pg_connect. It has some downsides so see the docs.
Also - check your memory usage, it may be you could
fix this by reducing work_mem or similar.
Regards,
Ben
"Najib Abi Fadel" [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]...Hi
Hi, the following sql returns 10 and not 20 as would be expected.
Where is the error? This would for sure work in any programming
language, why is this different?
CREATE FUNCTION test () RETURNS INTEGER AS '
DECLARE
k integer;
BEGIN
k = 10;
The most cumbersome part of our many (hundreds) of unit tests that
verify our data access is working, is creating and then deleting all
of the test data. Currently, we're doing it at a pretty high level
using java and our ORM (hibernate). It occurred to me that I can
probably speed the
What is the problem with pg_pconnect and is pgpool easy to use with an already running application ?Should i expect a major performance boost ? Najib."Joshua D. Drake" [EMAIL PROTECTED] wrote: Nikolay Samokhvalov wrote: As for persistent connection with PHP, start from here:
I have setup a view consisting of two tables and some compound fields.
Now I would like from the client perspective see what those compound
fields actually are originally.
Let's (for simplicity I have inlcuded only one table) say we have:
CREATE OR REPLACE VIEW clientview AS
SELECT c.clientid,
Title: RE: [GENERAL] serial column
Yes, but if I tear down the house at 245 Main St, we don't renumber 247 Main St and on down the line, do we?
The problem here is that even if you get the sequencing to work, your table is dangerously unstable. If you have 700 rows and you delete row #200,
Hi all :)
first, sorry for crossposting but dbilink mailinglist is extremely low traffic
so I decided to mail this also to pgsql-general
I'm looking for a solution that gives PostgreSQL remote server aka proxy
tables functionality.
We're trying here to evaluate dbi-link, and have some
On Tue, 2006-09-26 at 23:31, deep ... wrote:
Hi all,
I'm running a web application supported by postgresql 7.4 on an unix
system. The management part of application requires reindexing of
tables. I see that whenever reindexing runs with the while the table
is getting updated/inserted into,
We continue to have segmentation faults of the /usr/bin/postgres process
as I mentioned in an earlier thread. In all cases, the core file
always indicates a segmentation fault, but the backtraces don't seem to
consistently point to any particular problem. Then again, when you go
stomping
Joost Kraaijeveld [EMAIL PROTECTED] writes:
On Wed, 2006-09-27 at 12:10 -0400, Tom Lane wrote:
What you need to find out next is
what character set encoding that locale implies on your machine. I'm
betting it's not utf8 though :-(
If this is not what you mean, could you help me in the right
hi,
i support a trac [1] installation and changed the backend from sqlite to
postgres 8.1.4
the following sql stopped working with postgres, and the fix of this
problem seems strange to me.
first the old sql, that worked with sqlite:
SELECT
p.value AS __color__,
id AS ticket, summary, status,
On Tue, 2006-09-26 at 09:19, Tom Lane wrote:
Thomas Peter [EMAIL PROTECTED] writes:
the full code that does produce the error (and this error can be resolved
as in OP described) is:
Never oversimplify a bug report.
FROM ticket as t, permission as perm, enum as p
LEFT OUTER JOIN
On Wed, 2006-09-27 at 00:22, Najib Abi Fadel wrote:
What is the problem with pg_pconnect and is pgpool easy to use with an
already running application ?
Should i expect a major performance boost ?
pg_pconnect isn't pooling per se. In pooling, a large number of
processes share a small number
=?utf-8?B?5p6X6bub546J?= [EMAIL PROTECTED] writes:
Exception in thread main org.postgresql.util.PSQLException:
Connection refused. Check that the hostname and port are correct and
that the postmaster is accepting TCP/IP connections.
i have just now checked my postgre with command netstat -anp
Ronin [EMAIL PROTECTED] schrieb:
Hi, the following sql returns 10 and not 20 as would be expected.
Where is the error? This would for sure work in any programming
language, why is this different?
CREATE FUNCTION test () RETURNS INTEGER AS '
DECLARE
k integer;
Ronin [EMAIL PROTECTED] writes:
k = 10;
FOR k IN 1..10 LOOP
k = k +1;
END LOOP;
return k;
An integer for-loop implicitly declares its control variable, so the k
inside the loop is unrelated to the k outside.
Andrus wrote:
I'm bit new to Linux. I'm using white-box linux and Postgres 8.1.4
How to create backups of database with unique name in every night ?
Is there some script sample which can be called from /etc/crontab ?
I use the following Z-shell script. Rewriting to bash should be
trivial
Hi all, I want to
write an aggregate to sum the values for rain precipitations. I found a working
example with integer values, but I cannot find a way to to the same with float
ones. Here is what I did :
-- table testcreate table mytest (fld1 int4,
fld2 float4);insert into mytest values
Ups, missed the list recepient itself.
Thenx Duncan for the analysis.
This happend again, so I'm able to peek at the details you've pointed
out.
On Wed, 2006-09-27 at 09:33 -0700, Casey Duncan wrote:
Sounds like it was blocked (unsure by what). You can use pg_locks to
check that.
That view
I just finished reading one of Ralph Kimball's books. In it he
mentions something called a cyclical redundancy checksum (crc)
function. A crc function is a hash function that generates a checksum.
I am wondering a few things. A crc function would be extremely useful
and time saving in
On Sep 27, 2006, at 12:35 PM, Rafal Pietrak wrote:
Thenx Duncan for the analysis.
This happend again, so I'm able to peek at the details you've pointed
out.
On Wed, 2006-09-27 at 09:33 -0700, Casey Duncan wrote:
Sounds like it was blocked (unsure by what). You can use pg_locks to
check that.
is this what you mean?
testdb=# \d foo_view
View public.foo_view
Column | Type | Modifiers
+---+---
x | character varying(20) |
stuff | text |
y | character varying(20) |
View definition:
SELECT a.x,
First, I'm moving this to -general, because this is way off topic for
-hackers as near as I can tell.
On Tue, Sep 26, 2006 at 10:39:18PM +0200, Dragan Zubac wrote:
1. Is it possible for multiply PostgreSQL instances (engines,cores) to use
same DATA space?
No. In fact, this is a very good
Paolo Saudin [EMAIL PROTECTED] writes:
-- function for float (NOT WORKING)
create function myfloat4_sum(float4,float4) returns float4
as 'select float4pl($1,$2)::float4;'
language SQL;
-- aggregate
create aggregate myagg2_sum (basetype = float4, sfunc = myfloat4_sum, stype
= float4);
--
Karen Hill [EMAIL PROTECTED] writes:
Ralph Kimball states that this is a way to check for changes. You just
have an extra column for the crc checksum. When you go to update data,
generate a crc checksum and compare it to the one in the crc column.
If they are same, your data has not changed.
Gene Wirchenko wrote:
I just finished reading one of Ralph Kimball's books. In it he
mentions something called a cyclical redundancy checksum (crc)
function. A crc function is a hash function that generates a checksum.
I am wondering a few things. A crc function would be extremely
Someone posted an issue to the mod-perl list a few weeks ago about
their machine losing a ton of memory under a mod-perl2/apache/
postgres system - and only being able to reclaim it from reboots
A few weeks later I ran into some memory related problems, and
noticed a similar issue.
In response to Tom Lane [EMAIL PROTECTED]:
Karen Hill [EMAIL PROTECTED] writes:
Ralph Kimball states that this is a way to check for changes. You just
have an extra column for the crc checksum. When you go to update data,
generate a crc checksum and compare it to the one in the crc
Scott Marlowe [EMAIL PROTECTED] writes:
On Tue, 2006-09-26 at 09:19, Tom Lane wrote:
I was aware that MySQL parses this sort of structure wrongly, but it's
disappointing to hear that sqlite does too :-(
And I think MySQL fixed this abberant behaviour in the newest beta.
Actually they seem to
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 09/27/06 16:02, Karen Hill wrote:
Gene Wirchenko wrote:
[snip]
Yet what happens if there is a collision of the checksum for a row?
Then you get told that no change has occurred when one has. I
would call this an error.
That's exactly
When I have unix_socket_directory set to an alternate value, pg_ctl -
D data -w start times out. If I set it to default, it works fine.
I'm using postgresql 8.1.4 on FreeBSD.
Perhaps pg_ctl is waiting to see the socket file in /tmp/ before
reporting that postgresql successfully started?
Tom Lane wrote:
Karen Hill [EMAIL PROTECTED] writes:
Ralph Kimball states that this is a way to check for changes. You just
have an extra column for the crc checksum. When you go to update data,
generate a crc checksum and compare it to the one in the crc column.
If they are same, your
On Wed, 2006-09-27 at 14:26 -0700, Jeff Davis wrote:
When I have unix_socket_directory set to an alternate value, pg_ctl -
D data -w start times out. If I set it to default, it works fine.
I'm using postgresql 8.1.4 on FreeBSD.
Perhaps pg_ctl is waiting to see the socket file in /tmp/
Phillip Tornroth wrote:
In case it's useful information, the test database is pretty small...
Maybe 15 or 20 megs. The unit tests are individually pretty small.. Less
than 100 inserts each, for sure... So there's not that much to 'roll
back'.. As far as using transactions to undo the state of
On Monday 25 September 2006 15:05, Bob wrote:
I would like to use autonomous transactions for a large batch process and I
want this all encapsulated within stored procedures. I want to commit after
say every 15,000 records. The only way I have found to do this is to use
the perl DBI in my
On Monday 25 September 2006 07:48, Bo Lorentsen wrote:
Jeff Davis wrote:
Standby mode means that the database is kept almost up to date with the
master, but is not up. When the master goes down, you can bring the
standby machine up. Until then, you unfortunately can't even do read
queries
Hi all,
Using pgsql 8.0.1
I'm just starting with using the geometry data types in postgres, and ran into
what seems like a very basic problem. Namely, I can't seem to convert/cast
type text into type point when that text results from any expression. Ie, it
*only* works for a plain string
Jim Nasby [EMAIL PROTECTED] writes:
Take a look at http://pgfoundry.org/projects/autodoc/. I believe it uses
comments (ie: COMMENT ON) as well, so you can get some info into that.
Hi Jim!
Thanks for pointing me to the tool. The correct link to it is
http://www.rbt.ca/autodoc/.
It might
Is there any reason can't update to a newer version. Like 8.x?
Geoffrey wrote:
We continue to have segmentation faults of the /usr/bin/postgres process
as I mentioned in an earlier thread. In all cases, the core file
always indicates a segmentation fault, but the backtraces don't seem to
On Wed, Sep 27, 2006 at 03:50:46PM +0200, Filip RembiaÅkowski wrote:
Hi all :)
first, sorry for crossposting but dbilink mailinglist is extremely low traffic
so I decided to mail this also to pgsql-general
I'm looking for a solution that gives PostgreSQL remote server aka proxy
tables
It seems like it should be a very easy problem to solve I just need one role to have select privileges on all the tables of a particular schema or database including any new tables that are created since they are created programmatically daily. I've combed google and the docs to no avail. Do I
81 matches
Mail list logo
