when u connect to the database type:\h GRANTand you will get all the Grant options:GRANT { { CREATE | TEMPORARY | TEMP } [,...] | ALL [ PRIVILEGES ] } ON DATABASE dbname [, ...] TO { username | GROUP groupname | PUBLIC } [, ...] [ WITH GRANT OPTION ]This will grant the privileges on all tabl
Stepping back a bit...
Why not use an update trigger on the affected tables to record a
lastupdated timestamp value when the record is changed.
Surely this is simpler thanks computing some kind of row hash?
John
Karen Hill wrote:
Tom Lane wrote:
"Karen Hill" <[EMAIL PROTECTED]> writes:
Ra
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 need
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
>
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
>
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
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 li
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
>
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 s
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 t
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 /
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 ar
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?
Rega
-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
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
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 t
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. Starti
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 extrem
"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 c
"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
> = float
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
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.
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 determini
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 vie
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 (1
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 (p
"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" ou
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
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 w
"=?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 "ne
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 num
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 O
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, prio
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
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 aro
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 i
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 probl
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,
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, (c
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:> http://php.net/pg_p
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 dele
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;
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
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,
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 rein
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
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
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:
===
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 t
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 s
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, an
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
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 s
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 gi
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 oth
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 mo
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 l
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'
>
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 (
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
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: pgsql-genera
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 file
"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
-
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
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 oth
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:
> CRE
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
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
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] ~]$ c
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 outpu
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 tabl
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
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.
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:> M
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 b
[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]
mag
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
RAISE
[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 avail
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 fie
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: a
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
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 t
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
83 matches
Mail list logo
