Bob Pawley wrote:
This is basically what I have done. However it is not particularly
stable and is inelegant.
The serial number is close to what I need except it becomes tied to the
information.
The row numbering on the PG Admin version 1.6.1 performs the same
operation that I am looking f
Bob Pawley wrote:
>> I'm sure that PG Admin just generates the numbers in the GUI as it
>> displays them (as they're meaningless as persistent data).
> Perhaps - but they aren't necessarily meaningless as pure information.
Can't you just do the same - generate the numbers within your
application
I have an application running on a Tomcat cluster talking to a cluster of
Postgresql DBs using HA-JDBC. If one of the members drop out of the cluster
it is necessary to get that member back into sync with the rest of the
cluster, and I have an application specific piece of code that does that.
Al
On Mon, 4 Dec 2006, Stephen Woodbridge wrote:
Hi all,
I was wondering if anyone has unac.c which is the lib used in Text::Unaccent
built and wrap as a plpgsql stored procedure not using plperl. Or maybe there
is another general solution that I am no aware of.
I have one, don't remember whet
Stephen Woodbridge a écrit :
Or maybe there is another general solution that I am no aware of.
If you just want to remove accents from your string, you can
use :
to_ascii(convert(, 'LATIN9'), 'LATIN9')
It works very well AFAIAC.
(tip given on this list)
--
Arnaud
--
On Tue, 5 Dec 2006 10:18:21 +, David Goodenough <[EMAIL PROTECTED]> wrote:
[...]
> The first bits of the sync are done without locking the source tables, and
> I do these until I find less than some suitable threshold of records
> needing
> to be updated. Then I lock the source tables and
i have installed pgsql8.* on fc3 .. it has installed
well and test was successful.
while installing opennms i run the installer
#./installer -disU.. this lead to ca certain error regarding iplike.so
permission denied.
error message
Excep
Hi all,
i have a Debian Server here which is using an NTP server for time
synchronization. At the DST shifts, the server time is correctly set.
In the database on the server i have a table with a column which
contains timestamps but the type of the column is char(30). The
timestamps in this colum
On Tuesday 05 December 2006 10:57, Bernd Helmle wrote:
> On Tue, 5 Dec 2006 10:18:21 +, David Goodenough
> <[EMAIL PROTECTED]> wrote:
>
> [...]
>
> > The first bits of the sync are done without locking the source tables,
> > and I do these until I find less than some suitable threshold of recor
Faqeer ALI wrote:
i have installed pgsql8.* on fc3 .. it has installed
well and test was successful.
while installing opennms i run the installer
#./installer -disU.. this lead to ca certain error regarding
iplike.so permission denied.
er
Peter Bauer wrote:
Hi all,
i have a Debian Server here which is using an NTP server for time
synchronization. At the DST shifts, the server time is correctly set.
In the database on the server i have a table with a column which
contains timestamps but the type of the column is char(30).
I'm as
Anton wrote:
Hi. I have a performance problem with this simple query:
Please post to one list at a time Anton. I'll see you over on the
performance list.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 2: Don't 'kill -9' the po
David Goodenough wrote:
On Tuesday 05 December 2006 10:57, Bernd Helmle wrote:
On Tue, 5 Dec 2006 10:18:21 +, David Goodenough
<[EMAIL PROTECTED]> wrote:
The statements issued to lock each table is:-
LOCK TABLE table IN EXCLUSIVE MODE; SELECT 1 FROM table;
So why selecting '1' for each r
hi can we make a field auto incrementing field using Time Stamp data type
Hi. I have a performance problem with this simple query:
SELECT collect_time FROM n_traffic JOIN n_logins USING (login_id)
WHERE n_logins.account_id = '1655' ORDER BY collect_time LIMIT 1;
Limit (cost=0.00..2026.57 rows=1 width=8) (actual
time=5828.681..5
Hi. I have a performance problem with this simple query:
SELECT collect_time FROM n_traffic JOIN n_logins USING (login_id)
WHERE n_logins.account_id = '1655' ORDER BY collect_time LIMIT 1;
I must add that is occurs when there is no rows in n_traffic for these
login_id's. Where there is at least
On Tuesday 05 December 2006 12:03, Richard Huxton wrote:
> David Goodenough wrote:
> > On Tuesday 05 December 2006 10:57, Bernd Helmle wrote:
> >> On Tue, 5 Dec 2006 10:18:21 +, David Goodenough
> >>
> >> <[EMAIL PROTECTED]> wrote:
> >>> The statements issued to lock each table is:-
> >>> LOCK
am Tue, dem 05.12.2006, um 17:31:53 +0530 mailte deepak pal folgendes:
> hi can we make a field auto incrementing field using Time Stamp data type
On INSERT: set the default value for this field to now()
For Update: create a trigger to do this.
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz:
Hello all,
I'm not much of a database professional, so my questions might sound silly ;)
I was wondering if PostgreSQL authorization rules can be aplied on
specific rows of a given table. I mean, AFAIK the GRANT statement
cannot be used for such purpose.
The other way I looked into implement suc
am Tue, dem 05.12.2006, um 12:52:15 -0200 mailte Thiago Silva folgendes:
> Hello all,
> I'm not much of a database professional, so my questions might sound silly
> ;)
>
> I was wondering if PostgreSQL authorization rules can be aplied on
> specific rows of a given table. I mean, AFAIK the GRANT
Perhaps I can - it will be learning curve for me. However, the development
would be so much easier to apply if it were available in PostgreSQL in a
form similar to generating a serial column.
Bob
- Original Message -
From: "Alban Hertroys" <[EMAIL PROTECTED]>
To: "Bob Pawley" <[EMAIL
David Goodenough <[EMAIL PROTECTED]> writes:
> The statements issued to lock each table is:-
> LOCK TABLE table IN EXCLUSIVE MODE; SELECT 1 FROM table;
> (I am not quite sure why the SELECT 1 FROM table is there, it came with
> HA-JDBC as the code for the Postgresql dialect).
> I notice that this
Hello,
I'm having a major Vacuuming problem. I used to do a full vacuum every
morning on my postgres database to clean up empty space on a table but
because of it's size, the locking of the database causes my application
server to max out the database connections and causes database errors.
To f
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] says...
> >> I have copied the folders back to the base dir (like C:\PostgreSQL
> >> \data
> >> \base\16404) if that's step one but what after that?
> >
> > Just start Postgres. If the data dir is ok, it should run fine.
>
> It's unclear from you
Bob Pawley wrote:
Your missing the point.
I am creating a design system for industrial control.
The control devices need to be numbered. The numbers need to be
sequential. If the user deletes a device the numbers need to regenerate
to again become sequential and gapless.
How many control de
Hello,
I'm trying to assign a password for a postgres user. I've logged in as
postgres, issued "alter user my_user with password 'my_passwd'". Doing "select
* from pg_users" shows the new user with password assigned. However, when I
try to start psql as the new user: "psql -U my_user -W" and
Hi everyone,
I have a java app using pgsql 7.3 on unix platform where the latest
version of the app updates the width of a row in a table as a part of
upgrade. Since there is no straightforward 'modify' clause in
Postgresql 7.3, we're using
alter table ... add new_column type (width);
update ..
On Dec 5, 9:52 am, [EMAIL PROTECTED] ("Thiago Silva") wrote:
> Hello all,
> I'm not much of a database professional, so my questions might sound silly ;)
>
> I was wondering if PostgreSQL authorization rules can be aplied on
> specific rows of a given table. I mean, AFAIK the GRANT statement
> cann
sweet that worked!
thanks
Michael Glaesemann wrote:
> On Dec 4, 2006, at 23:52 , Ronin wrote:
>
> > Hi when I do the following function it fills 2 dates per day from 1970
> > to 2050, except that some months (typical 2 months per year) have 4
> > dates for one day. this is totally freaky.. I won
Hi all,
We are moving from MySQL to PgSQL in my organization and I would like
some input on what the best method is for working with one development
and one live database. We need to copy all data, structure information,
functions et.c. from the live database to the development database
every now a
We had the same problem recently on our data warehouse.
Check out the reindex and cluster commands.
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Schwenker,
Stephen
Sent: Monday, December 04, 2006 9:56 AM
To: pgsql-general@PostgreSQL.org
Subject: [G
Marina Olhovsky <[EMAIL PROTECTED]> schrieb:
>
> Hello,
>
> I'm trying to assign a password for a postgres user. I've logged in as
> postgres, issued "alter user my_user with password 'my_passwd'". Doing
> "select
> * from pg_users" shows the new user with password assigned. However, when I
On Tue, 2006-12-05 at 00:42, deep ... wrote:
> Hi everyone,
>
> I have a java app using pgsql 7.3 on unix platform where the latest
> version of the app updates the width of a row in a table as a part of
> upgrade. Since there is no straightforward 'modify' clause in
> Postgresql 7.3, we're using
Marina Olhovsky wrote:
Hello,
I'm trying to assign a password for a postgres user. I've logged in
as postgres, issued "alter user my_user with password 'my_passwd'".
Doing "select * from pg_users" shows the new user with password
assigned. However, when I try to start psql as the new user: "ps
Schwenker, Stephen wrote:
> I'm having a major Vacuuming problem. I used to do a full vacuum every
> morning on my postgres database to clean up empty space on a table but
> because of it's size, the locking of the database causes my application
> server to max out the database connections and ca
On Mon, 2006-12-04 at 09:56, Schwenker, Stephen wrote:
> Hello,
>
> I'm having a major Vacuuming problem. I used to do a full vacuum
> every morning on my postgres database to clean up empty space on a
> table but because of it's size, the locking of the database causes my
> application server t
"Schwenker, Stephen" <[EMAIL PROTECTED]> writes:
> To fix that problem, I have turned off the full vacuum and are just
> doing a standard analyze vacuum.
Good.
> No I'm getting very close to running
> out of space on my disks because the table keeps on growing and the
> database is not re-using d
On 5 Dec 2006 at 2:59, [EMAIL PROTECTED] wrote:
> live database. We need to copy all data, structure information,
> functions et.c. from the live database to the development database
> every now and then. The development database will be on a separate
> machine. What is the best way to do this?
On Tue, 2006-12-05 at 04:59, [EMAIL PROTECTED] wrote:
> Hi all,
> We are moving from MySQL to PgSQL in my organization and I would like
> some input on what the best method is for working with one development
> and one live database. We need to copy all data, structure information,
> functions et.c
[ The author's email address is clearly bogus, so I can't privately send
him email.]
You seems to be trying to get help by supplying the least amount of
information possible, and not even fully typing out words. Such
approaches usually fail.
I suggest we ignore further emails from this person un
Running Postgres version 8.1.3 on Opteron box running FreeBSD
6.0-RELEASE #10.
We stopped postgres using kill -TERM. When we tried to restart the
engine, it would not recover. The logs stated the following:
2006-12-04 10:18:39 CST LOG: archived transaction log file
"0001006900
2006/12/4, Ian Harding <[EMAIL PROTECTED]>:
On 11/13/06, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Mon, 2006-11-13 at 15:36, novnov wrote:
> > OK, thanks everyone, I gather from the responses that postgres performance
> > won't be an issue for me then. If MS SQL Server and Postgres are in the
Michael Glaesemann wrote:
-->-Mensagem original-
-->De: Michael Glaesemann [mailto:[EMAIL PROTECTED]
-->Enviada em: segunda-feira, 4 de dezembro de 2006 19:44
-->Para: Alejandro Michelin Salomon ( Adinet )
-->Cc: Pgsql-General
-->Assunto: Re: [GENERAL] Problem working with dates and times
To recover disk space, reindex the heavily updated tables. You can do
this while the database is in production.
Check the REINDEX command.
John
Schwenker, Stephen wrote:
Hello,
I'm having a major Vacuuming problem. I used to do a full vacuum every
morning on my postgres database to clean
andy rost <[EMAIL PROTECTED]> writes:
> We stopped postgres using kill -TERM. When we tried to restart the
> engine, it would not recover.
Since you're apparently using archiving, you could pull the missing xlog
files back from the archive no? Either manually, or automatically by
installing a re
"Tomi N/A" <[EMAIL PROTECTED]> writes:
> 2006/12/4, Ian Harding <[EMAIL PROTECTED]>:
>> Amen. When I migrated from MSSQL to PostgreSQL (4 years ago), I found
>> out exactly how seriously MS SQL coddles you when it comes to its "Oh,
>> I know what you really meant" query planning. I committed some
I'm pretty sure reindexing a table takes out an exclusive lock, which
means you might wanna wait til off hours to do one.
On Tue, 2006-12-05 at 13:26, John Sidney-Woollett wrote:
> To recover disk space, reindex the heavily updated tables. You can do
> this while the database is in production.
>
I get the following error installing the binary RPMS for RedHat es 4:
[EMAIL PROTECTED] postgres]# rpm -Uvh compat-postgresql-libs-4-1PGDG.i386.rpm
error: Failed dependencies:
libc.so.6(GLIBC_2.4) is needed by
compat-postgresql-libs-4-1PGDG.i386
libcrypto.so.6 is needed by compat-po
We perform a daily PTR backups of the database. Part of this process is
to delete old archived WALs between backups (no need to keep archived
transaction logs that are older than the most recent full backup, or is
there?). Since we had no indication of a problem, and since the server
continued
Thanks all for your very insightful and helpful answers. I will be
able to really spend some time thinking about how the db will evolve
(and so whether it is worth thinking about a change) in a week or so
and will be able to think more on your answers then.
Cheers
Antoine
andy rost <[EMAIL PROTECTED]> writes:
> I'm curious about a couple of things. Why didn't the logs reflect the
> problem that it noticed when it tried to restart on 2006-12-04(what I
> mean by that, is Postgres thought the server had been interrupted on
> 2006-12-02 16:45 yet the logs for that da
I have a table that has roughly 200,000 entries and many columns.
The query is very simple:
SELECT Field1, Field2, Field3... FieldN FROM TargetTable;
TargetTable has an index that is Field1.
The thing is on this machine with 1Gig Ram, the above query still takes
about 20 seconds to finish. And
Scott Marlowe wrote:
On Mon, 2006-12-04 at 09:56, Schwenker, Stephen wrote:
I'm using version 7.4.2 on solaris.
A few points:
4: Look at migrating to 8.1 or even 8.2 (due out real soon now). There
have been a lot of advances in pg since 7.4, and the upgrade is pretty
painless as long as the
On Tuesday 05 December 2006 3:56 pm, Wei Weng wrote:
> I have a table that has roughly 200,000 entries and many columns.
>
> The query is very simple:
>
> SELECT Field1, Field2, Field3... FieldN FROM TargetTable;
>
> TargetTable has an index that is Field1.
>
> The thing is on this machine with 1Gi
Forgot to mention the version I am using.
PostgreSQL 7.4.13
Thanks
On Tue, 2006-12-05 at 15:56 -0500, Wei Weng wrote:
> I have a table that has roughly 200,000 entries and many columns.
>
> The query is very simple:
>
> SELECT Field1, Field2, Field3... FieldN FROM TargetTable;
>
> TargetTabl
I am running this in the same machine as the database though.
Thanks
On Tue, 2006-12-05 at 16:02 -0500, Jan de Visser wrote:
> On Tuesday 05 December 2006 3:56 pm, Wei Weng wrote:
> > I have a table that has roughly 200,000 entries and many columns.
> >
> > The query is very simple:
> >
> > SELE
In response to Josh Berkus <[EMAIL PROTECTED]>:
> -- Online index builds
I'm particularly curious about this feature. Does this mean that
PostgreSQL 8.2 can perform a REINDEX without blocking the relevant
table from writes?
If so, the 8.2 docs are a bit out of date:
http://www.postgresql.org/do
On Tue, 2006-12-05 at 14:56, Wei Weng wrote:
> I have a table that has roughly 200,000 entries and many columns.
>
> The query is very simple:
>
> SELECT Field1, Field2, Field3... FieldN FROM TargetTable;
>
> TargetTable has an index that is Field1.
>
> The thing is on this machine with 1Gig Ra
On Tue, 2006-12-05 at 14:56, Glen Parker wrote:
> Scott Marlowe wrote:
> > On Mon, 2006-12-04 at 09:56, Schwenker, Stephen wrote:
> >> I'm using version 7.4.2 on solaris.
> >
> > A few points:
> >
> > 4: Look at migrating to 8.1 or even 8.2 (due out real soon now). There
> > have been a lot of
On Tue, 2006-12-05 at 16:06 -0500, Bill Moran wrote:
> In response to Josh Berkus <[EMAIL PROTECTED]>:
>
> > -- Online index builds
>
> I'm particularly curious about this feature. Does this mean that
> PostgreSQL 8.2 can perform a REINDEX without blocking the relevant
> table from writes?
I do
Bill Moran wrote:
> In response to Josh Berkus <[EMAIL PROTECTED]>:
>
> > -- Online index builds
>
> I'm particularly curious about this feature. Does this mean that
> PostgreSQL 8.2 can perform a REINDEX without blocking the relevant
> table from writes?
>
> If so, the 8.2 docs are a bit out o
On Dec 5, 2006, at 21:56 , Wei Weng wrote:
I have a table that has roughly 200,000 entries and many columns.
The query is very simple:
SELECT Field1, Field2, Field3... FieldN FROM TargetTable;
This is the very definition of a sequential scan: you're reading
200,000 rows from that table, an
In response to Alvaro Herrera <[EMAIL PROTECTED]>:
> Bill Moran wrote:
> > In response to Josh Berkus <[EMAIL PROTECTED]>:
> >
> > > -- Online index builds
> >
> > I'm particularly curious about this feature. Does this mean that
> > PostgreSQL 8.2 can perform a REINDEX without blocking the rele
On Tue, Dec 05, 2006 at 07:55:35AM -0800, Bob Pawley wrote:
> Perhaps I can - it will be learning curve for me. However, the development
> would be so much easier to apply if it were available in PostgreSQL in a
> form similar to generating a serial column.
Your assertion that it would be easy i
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On 12/05/06 14:56, Wei Weng wrote:
> I have a table that has roughly 200,000 entries and many columns.
>
> The query is very simple:
>
> SELECT Field1, Field2, Field3... FieldN FROM TargetTable;
>
> TargetTable has an index that is Field1.
>
> The
On þri, 2006-12-05 at 14:41 -0500, Josh Berkus wrote:
> After eight months of development and five months of integration and
> testing, the PostgreSQL Global Development Group now announces the
> availability of PostgreSQL version 8.2 (our 14th public release).
> ...
> For highlights of the rel
Ragnar,
Now that this has been announced, should not
http://www.postgresql.org/docs/current/ and co be
redirected to http://www.postgresql.org/docs/8.1/
instead of http://www.postgresql.org/docs/8.2/
in particular, the press release's link to the
Release Notes brought me to
http://www.postgr
On Tue, Dec 05, 2006 at 12:52:15 -0200,
Thiago Silva <[EMAIL PROTECTED]> wrote:
>
> I was wondering if PostgreSQL authorization rules can be aplied on
> specific rows of a given table. I mean, AFAIK the GRANT statement
> cannot be used for such purpose.
You can do this kind of thing using a vie
On þri, 2006-12-05 at 15:56 -0500, Wei Weng wrote:
> I have a table that has roughly 200,000 entries and many columns.
>
> SELECT Field1, Field2, Field3... FieldN FROM TargetTable;
> The thing is on this machine with 1Gig Ram, the above query still takes
> about 20 seconds to finish. And I need i
I suppose comparing postgres running on a single processor laptop to sql server
running
on a dual processor machine wouldn't help you determine what sql server does
better.
If it might let me know.
Aside from maybe having the planner reorder joins for you I would guess that it
is sql
servers s
2006/12/5, Tom Lane <[EMAIL PROTECTED]>:
These sorts of reports would be far more helpful if they contained some
specifics. What queries does MSSQL do better than Postgres, exactly?
You are of course correct, Tom.
I'm sorry I'm not in a position to replay what I've been doing a year
ago...I w
On Tue, 2006-12-05 at 16:32, Tomi N/A wrote:
> One type of query does come to mind, now that I think about it.
> pgsql has trouble handling queries like
> SELECT * FROM t0 WHERE t0.id_t1 IN (SELECT t1.id FROM t1 WHERE...)
> When the subselect returns a lot of results, pgsql really takes it's time
2006/12/5, Scott Marlowe <[EMAIL PROTECTED]>:
On Tue, 2006-12-05 at 16:32, Tomi N/A wrote:
> One type of query does come to mind, now that I think about it.
> pgsql has trouble handling queries like
> SELECT * FROM t0 WHERE t0.id_t1 IN (SELECT t1.id FROM t1 WHERE...)
> When the subselect return
I see that a new contrib module was added with the release of 8.2:
http://pgfoundry.org/projects/fulldisjunction/
I found this article that describes the set theory behind it:
http://dbpubs.stanford.edu:8090/pub/showDoc.Fulltext?lang=en&doc=1996-58&format=pdf&compression=&name=1996-58.pdf
Most of
> 1) what is its application?
I guess I found part of my "laymans" answer:
http://www.cs.toronto.edu/~yaron/Presentations/pods2003.ppt#468,39,Example
> 2) how is it used?
Maybe this question should have been, what is the syntax?
Regards,
Richard Broersma Jr.
---(end of b
Hi,
Tomi N/A wrote:
> When the subselect returns a lot of results, pgsql really takes it's
time.
8.1.something
PostgreSQL 8.2 improved a lot for IN clauses with lots of values. I
think it now performs as good as an equal join query.
Regards
Markus
---(end of broa
Does PostgreSQL lock the entire row in a table if I update only 1
column?
--
Groeten,
Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
web: www.askesis.nl
---(end of broadcast)---
TI
These sorts of reports would be far more helpful if they contained some
specifics. What queries does MSSQL do better than Postgres, exactly?
Our OR-patch was inspired by our customer migrating from MS SQL to postgres.
Next, index support of IS NULL. And, there is a huge difference in performan
Hi,
Joost Kraaijeveld wrote:
Does PostgreSQL lock the entire row in a table if I update only 1
column?
Yes. In PostgreSQL, an update is much like a delete + insert. A
concurrent transaction will still see the old row. Thus the lock only
prevents other writing transactions, not readers.
Reg
79 matches
Mail list logo
