On Nov 15, 2006, at 12:41 PM, Dan Armbrust wrote:
I'm trying to convert a database from either MS Access or MySQL
into Postgres. I have found a couple of tools that will almost do
what I want - but not quite. To make things match up with code
that is already written - I need to have all o
Also take a look at the queries that psql performs for \d (start psql
with the -E option).
On Nov 15, 2006, at 2:11 PM, Jerry Sievers wrote:
"Ilja Golshtein" <[EMAIL PROTECTED]> writes:
Hello!
How could I find out if a temporary table
(or index on a temporary table) was created
by current
On Nov 15, 2006, at 2:07 AM, Alban Hertroys wrote:
I suppose the real question is this: As it is not possible to
initialize
a %ROWTYPE type variable to NULL, is comparing it to NULL valid or
is it
comparing apples and oranges? Does it yield the expected result
(true if
the %ROWTYPE variabl
Jeff Davis <[EMAIL PROTECTED]> writes:
> What am I missing about EXPLAIN ANALYZE that is causing that much
> overhead?
Instrumentation overhead? In this case you've got 2 million more
gettimeofday() calls in the one case than the other, which says that
gettimeofday() takes about 1 microsecond on
Jeff Davis <[EMAIL PROTECTED]> writes:
> Interesting question. It seems like you're trying to make a default
> value for a view that's based on the underlying table's default value.
> I think the normal way to do this is to _not_ have a default value on
> the underlying table, and instead use the
The manual suggests: iconv -c -f UTF-8 -t UTF-8 -o cleanfile.sql
dumpfile.sql. The -c option removes invalid character sequences. A diff
of the two files will show the sequences that are invalid. iconv reads
the entire input file into memory so it might be necessary to use split
to break up the dum
Thanks for the suggestion ... since the data involved came from different
source, I suspect there may be more than one encoding, but this has great
promise.
Greg
-Original Message-
From: Russell Smith [mailto:[EMAIL PROTECTED]
Sent: Thu 11/16/2006 7:27 PM
To: Gregory S. William
Gregory S. Williamson wrote:
Dear list,
I have been banging my head against a problem for a few days now, and although
I am making progress it is painfully slow, and I am hoping that some one out
there can steer me in a better way.
I've got a medium size database (7.4), about 64 gigs of data,
Version 8.2beta3.
If I EXPLAIN ANALYZE the query, it reports a time of about 2600 ms. If I
run the query in psql, I can tell visibly that the query takes less time
to execute than 2 seconds. Even stranger, if I wrap the query in a SQL
function and EXPLAIN ANALYZE a query that selects from the SQL
On Thu, 2006-11-16 at 16:58 -0800, Jeremy Smith wrote:
>
> One more thing that would sweeten the deal even further! Not so much
> for sequences, but for other columns with default values:
>
> insert into foo(bar) values(COALESCE(new.bar, DEFAULT))
>
> This doesn't work, because DEFAULT is a lan
Dear list,
I have been banging my head against a problem for a few days now, and although
I am making progress it is painfully slow, and I am hoping that some one out
there can steer me in a better way.
I've got a medium size database (7.4), about 64 gigs of data, about 1/3rd of
which is in on
On 11/16/06, Jeremy Smith <[EMAIL PROTECTED]> wrote:
On 11/16/06, Jeff Davis <[EMAIL PROTECTED]> wrote:
> create rule "child_with_parent_explicit_insert" as
> on insert to child_with_parent_explicit do instead (
> insert into parent(id, foo) values(COALESCE
> ( new.id
On 11/16/06, Jeff Davis <[EMAIL PROTECTED]> wrote:
create rule "child_with_parent_explicit_insert" as
on insert to child_with_parent_explicit do instead (
insert into parent(id, foo) values(COALESCE
(new.id,NEXTVAL('parent_id_seq')), new.foo);
insert into
On Thu, 2006-11-16 at 11:34 -0800, Jeremy Smith wrote:
> Example:
>
> Begin example SQL
> create table parent (
> id serial primary key,
> foo integer,
> );
>
> create table child (
> id integer references parent(id) on delete cascade,
> bar intege
On 11/17/06, Tom Lane <[EMAIL PROTECTED]> wrote:
"Gurjeet Singh" <[EMAIL PROTECTED]> writes:
> Small implementation detail: Also keep a count of how many times the
same
> session requested the same lock, and do not release the lock until he
> requests same number of releases.
No need for that,
"Gurjeet Singh" <[EMAIL PROTECTED]> writes:
> On 11/17/06, Tom Lane <[EMAIL PROTECTED]> wrote:
>> we need a special case when we are already a member of the MultiXact:
>> fall through without trying to reacquire the tuple lock.
> Small implementation detail: Also keep a count of how many times the
On Thu, Nov 16, 2006 at 12:40:41PM -0800, Glen Parker wrote:
> But now, pull the drive from port 2 and boot the system. You will now
> have SDA,SDB,SDC. The kernel will now fail BOTH of the last two drives
> from the RAID array. The one that was SDC is gone, and obviously fails.
> The one th
On Thu, Nov 16, 2006 at 02:24:43PM -0500, beer wrote:
> Hello
>
> I know this isnt specifically a postgres question, but people here
> are much more likely to have an answer than on a perl list. I have
> an app in perl that uses DBD::Pg to access the database. This module
> does not seem to have
On 11/17/06, Tom Lane <[EMAIL PROTECTED]> wrote:
we need a special case when we are already a member of the MultiXact:
fall through without trying to reacquire the tuple lock.
Small implementation detail: Also keep a count of how many times the same
session requested the same lock, and do not
Hi all, I apologize for asking a Linux question here in the postgres
list, but I figure somebody here must know the answer, or a better place
to ask it.
I am building an x86_64 postgres server (see, it isn't JUST about Linux
:-) with SATA drives and I want to use software RAID for my postgres
Martijn van Oosterhout writes:
> That said, do you have to specify the library kernel32? If you use
> language "internal", doesn't that find it since it's already loaded?
This has come up before:
http://archives.postgresql.org/pgsql-hackers/2006-06/msg00721.php
That particular thread died off wh
Clarence Gardner <[EMAIL PROTECTED]> writes:
> That scenario seems quite simple, but I can't reproduce the deadlock with
> this seemingly-identical sequence.
This is a bug in 8.1 and up. The reason you couldn't reproduce it is
that it requires a minimum of three transactions involved, two of whic
Hello
I know this isnt specifically a postgres question, but people here are much
more likely to have an answer than on a perl list. I have an app in perl that
uses DBD::Pg to access the database. This module does not seem to have support
for using certificate authentication. I'm hoping some
A question about rules:
I am trying to create a rule which handles inserts on a view, and that
rule should insert rows into multiple tables, each one of which
contains some of the columns that the view contains.
What if one of these tables has, for example, a serial primary key?
If I explicitly
Hi Tom,
Yeah, that's what I suspect it seems more like a hardware/os issue.
Since, I really have no proof against PostgreSQL, other
than my daily dump that crashes *sometimes*.
I didn't know about badblocks, I'll try this one. Last time I did a full
fsck check on all the volumes, and everythi
duh! right. I didn't thought about this one!! but the strange thing
though is that it doesn't happen frequently, only recently it started to
crash regularly.
here's the content of the crontab:
SHELL=/bin/bash
PATH=/sbin:/bin:/usr/sbin:/usr/bin
MAILTO=root
HOME=/
# run-parts
01 * * * * root ru
For the column: date(varchar 8) '2000606' the
SELECT ascii(substring(date,4,1)) from jnlsale WHERE id_jnlsale=28384
gives: 22
SELECT ascii('6'); gives 54
For the column: amount(float 8) 1.20932764209866e-307 the
SELECT ascii(substring(amount,20,1)) from jnlsale WHERE id_jnlsale=28382
gives: 48
Tom Lane wrote:
"Florian G. Pflug" <[EMAIL PROTECTED]> writes:
I'm looking for a way to recover deleted or old versions of
accidentally updated rows from a postgres 7.4 database. I've
verified that the relevant tables haven't been vacuumed since
the accident took place.
I was thinking that it
"Christian Rengstl" <[EMAIL PROTECTED]> writes:
> I have a partial index on the expression where not allele_1=allele_2
What is that index's definition *exactly*? No handwaving please, let's
see the SQL. Also, what datatypes are these columns?
regards, tom lane
-
Ardian Xharra wrote:
No, sorry about the name of the table it's the same table.
Yes, the change is in live database during a select
OK, so it's definitely a data change. Can you get the numeric value of
the character in question, and compare it to the original value
SELECT ascii(substring(m
=?iso-8859-2?Q?Marcin_Ma=F1k?= <[EMAIL PROTECTED]> writes:
> I have an unconfirmed feeling that autovac does not like system-wide
> statement_timeout.
If you have it set to less than the time needed to do a vacuum, then
yes, autovac will fail. You expected differently? Do you think it's
a good i
Marcin Mañk <[EMAIL PROTECTED]> writes:
> Hello.
> I have an unconfirmed feeling that autovac does not like system-wide
> statement_timeout. I.e. when I in some panic move set system-wide
> statement_timeout to 90 seconds, autovac stopped working (I do not know for
> 100% if there is a dependency)
Hi all,
I have some doubts about SPI (Server Programming Interface). In
fact, I must create Stored Procedures in Postgres (8.0). The problem
is: all stored procedures and, may be, some triggers must be compiled
'cause the clients can't access the source code of them.
To solve this prob
Christian Rengstl wrote:
Hi everyone,
i have a function that looks executes the following command in a while loop in
which it iterates through tables (aTable):
FOR rec in EXECUTE 'SELECT count(a.allele_1) as c from aTable a INNER JOIN
map_table b on(a.snp_id=upper(b.snp_id)) WHERE NOT a.allele
When I configure statement_timeout globally, I typically override it
for superusers and other accounts used by dbas. Just issue:
ALTER USER postgres SET statement_timeout = 0;
Repeat for other superusers (slony, etc). Then the policy won't apply
to them.
-Casey
On Nov 16, 2006, at 6:46
Eric Rousse <[EMAIL PROTECTED]> writes:
> ...
> 2006-11-16 04:00:39 [8763] LOG: connection received: host=10.1.1.54
> port=4894
> 2006-11-16 04:00:40 [8763] LOG: pq_recvbuf: unexpected EOF on client
> connection
> 2006-11-16 04:00:40 [8763] LOG: incomplete startup packet
> 2006-11-16 04:
Hello.
I have an unconfirmed feeling that autovac does not like system-wide
statement_timeout. I.e. when I in some panic move set system-wide
statement_timeout to 90 seconds, autovac stopped working (I do not know for
100% if there is a dependency).
Ups... Now I checked that pg_dump has the same i
Hi everyone,
i have a function that looks executes the following command in a while loop in
which it iterates through tables (aTable):
FOR rec in EXECUTE 'SELECT count(a.allele_1) as c from aTable a INNER JOIN
map_table b on(a.snp_id=upper(b.snp_id)) WHERE NOT a.allele_1=a.allele_2 and
b.gene=s
Eric Rousse wrote:
Hello all,
I've been experiencing strange crash, never really took care of it since
it was happening only every 1-2 months or so. But lately, I've seen it a
lot in the past week and I have no clue about it, other than the backups.
So, here's some info about it and about my
On Thursday November 16 2006 3:33 am, Richard Huxton wrote:
> Ed L. wrote:
> > One idea would be to partition the table some how such that
> > the chunks getting vacuumed are much smaller and thus not
> > such an impact. On the app side, I suppose we could break
> > the table into multiple tables
No, sorry about the name of the table it's the same table.
Yes, the change is in live database during a select
Regards, Ardian
- Original Message -
From: "Richard Huxton"
To: "Ardian Xharra" <[EMAIL PROTECTED]>
Cc: "postgreSQL postgreSQL"
Sent: Thursday, November 16, 2006 12:30 PM
Su
Hello all,
I've been experiencing strange crash, never really took care of it since
it was happening only every 1-2 months or so. But lately, I've seen it a
lot in the past week and I have no clue about it, other than the backups.
So, here's some info about it and about my machine:
When: it
I have the following result from the sql below. Can anyone help by
explaining why the last record could be returned. Periodstart and
periodend are time data types. I have tried to cast '10.35' to a time
type with the same results.
___
Ardian Xharra wrote:
Hello,
We been having a problem lately with some data changing their value without
modifying them.
We used to backups to see the difference:
this was the first backup
INSERT INTO journal VALUES
and the second backup
INSERT INTO jnlsale VALUES
These are different table
"Ed L." <[EMAIL PROTECTED]> writes:
> On Tuesday November 14 2006 12:56 pm, Jim C. Nasby wrote:
>> You don't have the vacuum cost delay settings set unreasonably
>> high, do you?
> I'm not sure. Here's what we're running:
> autovacuum_vacuum_cost_delay = 500 # default vacuum cost delay for
Hello,
We been having a problem lately with some data changing their value without
modifying them.
We used to backups to see the difference:
this was the first backup
INSERT INTO journal
VALUES(28382,698,754,7116,7,0.01,'20060606','15415773',1,3,4,1,1,5,77,1,17,2,1,1,15,1,1,2,0,32252,0,14183,0,
> I have a table with a sequence as its primary key.
> On insert I would like to have all its fields (except the primary key)
> default to the last one in the table (order by id desc limit 1).
> I guess I should use a trigger to do this but I don't know how.
How about this:
INSERT INTO table
SELE
Igor Shevchenko <[EMAIL PROTECTED]> writes:
> I've got a problem w/ one of pgsql installations. It can't start:
> [EMAIL PROTECTED] ~]$ /usr/pgsql/bin/postgres -D /usr/pgsql/data
> PANIC: failed to re-find parent key in "23724"
> Aborted
Try applying this patch:
http://archives.postgresql.org/pg
am Thu, dem 16.11.2006, um 16:05:15 +0530 mailte deepak pal folgendes:
> hi i want to convert 238.000 to 238.0 value 238.000 i got from database how to
> use to_char() function...??
Use round() instead.
test=> select round(238.000::numeric, 1);
round
---
238.0
(1 row)
Andreas
i have been able to generate the rpms for both 32 bit and 64 bit architectures,
for postgres 8.1.5 FC4
thanks,
regards
Surabhi
From: Devrim GUNDUZ [mailto:[EMAIL PROTECTED]
Sent: Thu 11/16/2006 11:16 AM
To: surabhi.ahuja
Cc: Clodoaldo Pinto Neto; pgsql-general@p
hi i want to convert 238.000 to 238.0 value 238.000 i got from database how
to use to_char() function...??
Ed L. wrote:
One idea would be to partition the table some how such that the
chunks getting vacuumed are much smaller and thus not such an
impact. On the app side, I suppose we could break the table
into multiple tables on some dimension (time) to make the vacuum
impacts smaller.
You're run
[EMAIL PROTECTED] wrote:
I am using 8.1.5, which doesn't have IF EXISTS yet. Although, this is a
good addition to the language.
How do I ignore the error? The problem is that I would like to have a
set of drop/create statements executed at once and one drop failing
will abort the script executi
Dear Richard. I've now solved reading a French forum.
I obtained an error 1069 service cannot start due to logon failure when
I tried to start the server manually.
Now: it's sufficient to go in the options of the user postgres (Local
Users->postgres) and change the settings for the password to set
SunWuKung wrote:
I have a table with a sequence as its primary key.
On insert I would like to have all its fields (except the primary key)
default to the last one in the table (order by id desc limit 1).
I guess I should use a trigger to do this but I don't know how.
I'm not sure you can really
Albert wrote:
Hi all! I have windows XP professional 2003. Yesterday evening I
installed, while turning off my pc, some updates of the os and this
morning the database server does not start!! May some have a trick to
help me? I fund something on the web but too unspecific...
What were the updat
surabhi.ahuja wrote:
Hi
I have downloaded Postgres 8.1.4 for FC4 x86_64 bit arch. and
installed on my m/c
Earlier this machine had Postgres 8.0.0. However that time It was the
normal 32 bit server.
I build my project (which has dependency on libpq.so) and so i have
the libraries which i copy o
Hi all! I have windows XP professional 2003. Yesterday evening I
installed, while turning off my pc, some updates of the os and this
morning the database server does not start!! May some have a trick to
help me? I fund something on the web but too unspecific...
Thanks!!
-
> I have downloaded Postgres 8.1.4 for FC4 x86_64 bit arch.
> and installed on my m/c
>
> Earlier this machine had Postgres 8.0.0. However that time It
> was the normal 32 bit server.
>
> I build my project (which has dependency on libpq.so)
> and so i have the libraries which i copy onto the
George Pavlov wrote:
>> For larger tables, you may have to resort to a
>> union:
>>
>>select * from foo where name != 'Other' order by name
>>union
>>select * from foo where name = 'Other'
>
> Alas, this suggestion is wrong on two counts: (a) UNION expects a single
> ORDER BY that ap
I have a table with a sequence as its primary key.
On insert I would like to have all its fields (except the primary key)
default to the last one in the table (order by id desc limit 1).
I guess I should use a trigger to do this but I don't know how.
Thanks for the help.
SWK
61 matches
Mail list logo
