On Jun 13, 3:13 pm, "Andrus" <[EMAIL PROTECTED]> wrote:
(...)
> As I understand, only way to optimize the statement
>
> delete from firma1.rid where dokumnr not in (select dokumnr from
> firma1.dok);
>
> assuming that firma1.dok.dokumnr does not contain null values is to change
> it to
>
> CREATE
am Thu, dem 14.06.2007, um 10:57:43 -0700 mailte Lza folgendes:
> Can anyone help me with this problem?
>
> I have a table in my database that holds information on policies and
> this table is linked to a number of other tables. I need to be able to
> keep a history of all changes to a policy ove
On Thu, 14 Jun 2007, Lza wrote:
When I run a query on the policy table for a certain period, I also need
to be able to pull the correct related rows (i.e. the information that
would have been in the table at that time) from the tables linked to it.
Check out "Developing Time-Oriented Database
Francisco Reyes <[EMAIL PROTECTED]> writes:
> Tom Lane writes:
>> Can we see the context-sizes dump that should've come out right before
>> that in the log?
> Is this the one you need?
No. [squints...] Hm, you're looking at a syslog log, aren't you.
The memory dump only comes out on stderr (I t
"Andrus" <[EMAIL PROTECTED]> writes:
> PostgreSQL runs commands
> create table test ( test integer primary key );
> create index i1 on test(test);
> create index i2 on test(test);
> without any error.
> Now there are 3 same indexes on table.
> How to fix this so that duplicate indexes are not all
Can anyone help me with this problem?
I have a table in my database that holds information on policies and
this table is linked to a number of other tables. I need to be able to
keep a history of all changes to a policy over time. The other tables
that are linked to policy also need to store histo
On Wed, Jun 13, 2007 at 09:45:52AM -0400, Tom Lane wrote:
> Joost Kraaijeveld <[EMAIL PROTECTED]> writes:
> > I have moved my database files from their default location to their own
> > partition on with their own controller and disks. PostgreSQL works OK
> > and I can connect with Pgadmin (Debian
PostgreSQL runs commands
create table test ( test integer primary key );
create index i1 on test(test);
create index i2 on test(test);
without any error.
Now there are 3 same indexes on table.
How to fix this so that duplicate indexes are not allowed ?
Andrus.
---(end
hello,
I try to allocate a chunk of ids from a sequence with the following
proc. However, if I don't use the 'lock lock_table', the proc may not
work when it runs at the same time by different psql sessions. Is there
a better way without using the 'lock lock_table' ?
Thanks,
Gary
create o
from pgAdmin, it takes 1 second.
When I run this command from script it takes 11 minutes!
Any idea why running this command from script takes 11 minutes?
Different plans maybe? Try EXPLAIN ANALYZE in both cases.
Thank you. I tried
explain analyze SELECT r.dokumnr FROM rid r LEFT JOIN do
On 14 Giu, 12:38, [EMAIL PROTECTED] (Marco Colombo) wrote:
> While PG has tons more features than SQLite, the major question here is:
> do you really need a database _server_? One thing that PG is designed
> for is handling many (as in 100) concurrent users. Database users, that
> is, meaning proc
Dear group,
I would like to announce a Linq Provider for Postgres
(i.e. a driver for C# 3.0 Language-Integrated Query - which is an O/R
mapping layer).
Here is the project page:
http://code2code.net/DB_Linq/index.html
Or go straight to the code:
http://code.google.com/p/dblinq2007/
Limitations:
and script terminates after 5.5 hours running yesterday night.
This might be a good reason not to run the script as a single long
transaction --- it's probably accumulating locks on a lot of different
tables. Which would be fine if it was the only thing going on, but
evidently it isn't.
Thank
On May 11, 12:08 pm, [EMAIL PROTECTED] ("Joshua D. Drake") wrote:
> Geoffrey wrote:
>
> People still use OpenVMS? ... elitist isn't the word I would choose ;)
>
Not only do they use it, new books get written about doing application
development with it. It is still the only OS able to create a fau
visit http://www.postgresql.org/community/lists/subscribe
2007/6/14, Manso Gomez, Ramon <[EMAIL PROTECTED]>:
How can delete my suscription to this forums?
-Mensaje original-
De: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] En nombre de Jyoti Seth
Enviado el: jueves, 14 de junio de 2007
My 2 cents:
I used to get a lot of these sharelock problems.
Users using different records, but same tables in different order.
(apparently 7.x was not as good as 8.x at row level locking)
I was advised to upgrade from 7.x to 8.x
I did, and all those sharelock problems went away.
Terry
Terry F
Terry Fielder wrote:
My 2 cents:
I used to get a lot of these sharelock problems.
Users using different records, but same tables in different order.
(apparently 7.x was not as good as 8.x at row level locking)
I was advised to upgrade from 7.x to 8.x
I did, and all those sharelock problems wen
Tom Allison wrote:
Gregory Stark wrote:
I'm still not precisely clear what's going on, it might help if you
posted the
actual schema and the deadlock message which lists the precise locks that
deadlocked.
Are any of the DML you mention on other tables on those tables with
foreign
key refe
Although I rarely see it mentioned, Skype has some replication tools that
they opensourced.
https://developer.skype.com/SkypeGarage/DbProjects/SkyTools
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Gregory Stark wrote:
I'm still not precisely clear what's going on, it might help if you posted the
actual schema and the deadlock message which lists the precise locks that
deadlocked.
Are any of the DML you mention on other tables on those tables with foreign
key references to this one?
It's
Gregory Stark writes:
What does the output of "ulimit -a" show?
In FreeBSD, as far as I know, what controls the size of a program is the
/boot/loader.conf and /etc/login.conf
The default /etc/login.conf has unlimited size.
/boot/loader.conf is set to max program size of 1.6GB
Can you ar
Gregory Stark wrote:
I'm still not precisely clear what's going on, it might help if you posted the
actual schema and the deadlock message which lists the precise locks that
deadlocked.
Are any of the DML you mention on other tables on those tables with foreign
key references to this one?
It's
"Francisco Reyes" <[EMAIL PROTECTED]> writes:
> kern.maxdsiz="1600MB" #1.6GB
> kern.dfldsiz="1600MB" #1.6GB
> kern.maxssiz="128M" # 128MB
It ought to be maxdsiz which seems large enough.
> The error that I got was:
> pg_restore: [archiver (db)] Error from TOC entry 2146; 0 988154 TABLE DATA
> m
"Mason Hale" <[EMAIL PROTECTED]> writes:
> The default_statistics_target was originally 200.
> I upped it to 1000 and still get the same results.
You did analyze the table after upping the target right? Actually I would
expect you would be better off not raising it so high globally and just
raisi
Tom Lane writes:
Francisco Reyes <[EMAIL PROTECTED]> writes:
Syslog was:
Jun 14 10:17:56 bk20 postgres[7294]: [130-1] ERROR: out of memory
Jun 14 10:17:56 bk20 postgres[7294]: [130-2] DETAIL: Failed on request of size
134217728.
Can we see the context-sizes dump that should've come out rig
"Mason Hale" <[EMAIL PROTECTED]> writes:
>> What's the actual number of groups
>> (target_page_id values)?
> Approximately 40 million (I'll have a more precise number when the query
> finishes running ).
Ouch. The discrepancy between that and the 550K estimate is what's
killing you --- the hash
I should have mentioned this previously: running the same query against the
same data on 8.1.5 does not result in a hash aggregate plan or an out of
memory error. (Note: the hardware is different but very similar -- the main
difference is the 8.1.9 server (with the error) has faster disks)
On 6/1
Thanks Tom. Here's more info:
What have you got work_mem set to?
40960
What's the actual number of groups
(target_page_id values)?
Approximately 40 million (I'll have a more precise number when the query
finishes running ).
Maybe this helps?
crystal=> select null_frac, n_distinct, correl
"Mason Hale" <[EMAIL PROTECTED]> writes:
> SELECT target_page_id, min(created_at)
> FROM page_page_link
> GROUP BY 1;
> The page_page_link table has ~130 million rows.
> After analyzing the table, the planner picks a hash aggregate plan, which
> results in an out of memory error.
What have you g
On Jun 14, 12:57 pm, Lza <[EMAIL PROTECTED]> wrote:
> Can anyone help me with this problem?
>
> I have a table in my database that holds information on policies and
> this table is linked to a number of other tables. I need to be able to
> keep a history of all changes to a policy over time. The ot
Francisco Reyes <[EMAIL PROTECTED]> writes:
> Syslog was:
> Jun 14 10:17:56 bk20 postgres[7294]: [130-1] ERROR: out of memory
> Jun 14 10:17:56 bk20 postgres[7294]: [130-2] DETAIL: Failed on request of
> size 134217728.
Can we see the context-sizes dump that should've come out right before
that
I am trying to restore a file that was done with pg_dump -Fc
pg_dump on a postgreql 8.1.4 machine
pg_restore on a postgresql 8.2.4 machine.
The restore machine has the following settings.
/etc/sysctl.conf (FreeBSD machine)
kern.ipc.shmall=262144
kern.ipc.shmmax=534773760 #510MB
kern.ipc.semmap=25
With Postgresql 8.1.9 -- I have a simple group by query:
SELECT target_page_id, min(created_at)
FROM page_page_link
GROUP BY 1;
The page_page_link table has ~130 million rows.
After analyzing the table, the planner picks a hash aggregate plan, which
results in an out of memory error.
crystal=>
Something like this will help:
CREATE OR REPLACE FUNCTION import_text_file(char(255)) RETURNS void AS $$
DECLARE
filename ALIAS FOR $1;
fin varchar;
BEGIN
fin := 'COPY table from ' || filename;
execute fin;
END;
$$ LANGUAGE plpgsql;
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
Warren wrote:
How do I get a non superuser user to be able to run the COPY command?
You can copy to / from the stdin.
non-superusers cannot run copy to / from a file, since the copy to /
from a file does so with the access authority of the postgres user and
could be used to do "bad things" T
Warren <[EMAIL PROTECTED]> writes:
> Is there any way to make this function work?
> CREATE OR REPLACE FUNCTION import_text_file(char(255)) RETURNS void AS $$
> DECLARE
> filename ALIAS FOR $1;
> BEGIN
> COPY table FROM filename;
> END;
> $$ LANGUAGE plpgsql;
Use EXECUTE.
Hi,
I've got a table that stores private messages (like e-mails). Every row
has a source_user, a destination_user, a timestamp, and information
on whether the source and/or the destination user has already deleted
the message.
> CREATE TABLE privs (
> id serial NOT NULL,
> src_usrid integer
Is there any way to make this function work?
CREATE OR REPLACE FUNCTION import_text_file(char(255)) RETURNS void AS $$
DECLARE
filename ALIAS FOR $1;
BEGIN
COPY table FROM filename;
END;
$$ LANGUAGE plpgsql;
The version below works fine, but I need something like the above version.
CREA
How do I get a non superuser user to be able to run the COPY command?
--
Thanks,
Warren
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do
Alvaro Herrera wrote:
>
> I meant the queries inside the function.
>
>
Oh ;(
Here it is
"HashAggregate (cost=825.10..825.19 rows=1 width=112) (actual
time=59175.752..59176.301 rows=75 loops=1)"
" -> Nested Loop Left Join (cost=443.57..825.06 rows=1 width=112)
(actual time=148.338..58997.5
Rikard Pavelic wrote:
> Alvaro Herrera wrote:
> > Try doing a PREPARE and then EXPLAIN EXECUTE, like
> >
> > alvherre=# prepare foo as select generate_series(1, $1);
> Hm, no luck ;(
>
> prepare f1 as select * from
> raspored.dohvati_statistiku_rada_u_smjenama_radnika($1, $2);
> explain analyze e
On 6/14/07, Tom Lane <[EMAIL PROTECTED]> wrote:
This is intentional --- implicit casts to text are gone. You should be
happy that the above now fails, because it's calling your attention to
the fact that you've got very ill-defined semantics there. Is the
thanks for clarification. actually
Alvaro Herrera wrote:
> Try doing a PREPARE and then EXPLAIN EXECUTE, like
>
> alvherre=# prepare foo as select generate_series(1, $1);
> PREPARE
>
> alvherre=# explain analyze execute foo(100);
> QUERY PLAN
>
> --
Gregory Stark wrote:
>
> I'm still not precisely clear what's going on, it might help if you posted the
> actual schema and the deadlock message which lists the precise locks that
> deadlocked.
>
> Are any of the DML you mention on other tables on those tables with foreign
> key references to thi
Rikard Pavelic wrote:
> Is this possible?
>
> I've been searching posts, but to no luck ;(
>
> I have one SQL query inside function, but when
> i do select from function it takes 8 sec.
> If I execute just SQL query (with some parameters passed to it)
> it takes 0.3 seconds.
Try doing a PREPARE
I'm still not precisely clear what's going on, it might help if you posted the
actual schema and the deadlock message which lists the precise locks that
deadlocked.
Are any of the DML you mention on other tables on those tables with foreign
key references to this one?
It's impossible for two ins
On Thu, Jun 14, 2007 at 09:53:42AM -0700, Ben wrote:
> Because I figured it would be less hacky to have each postgres cluster
> listen on the address I want it to listen on, rather then listening on
> everything and then patching it up with iptables. But I see now that's not
> the case.
It is p
On Thu, 14 Jun 2007, Robin Ericsson wrote:
Why not running them on different ports and proxy the incoming ports
via iptables or something like that based on the current situation on
your backends?
Because I figured it would be less hacky to have each postgres cluster
listen on the address I w
Is this possible?
I've been searching posts, but to no luck ;(
I have one SQL query inside function, but when
i do select from function it takes 8 sec.
If I execute just SQL query (with some parameters passed to it)
it takes 0.3 seconds.
What I'm trying to do is select part of the data from the
In response to <[EMAIL PROTECTED]>:
>
> On 6/14/2007, "Gregory Stark" <[EMAIL PROTECTED]> wrote:
>
> >
> >
> ><[EMAIL PROTECTED]> writes:
> >
> >> But everyone once in a long while it seems that I hit simultaneaous
> >> execute() statements that deadlock on the insertion.
> >
> >What version of P
On Thu, 14 Jun 2007, Frank Wittig wrote:
But I also agree that if there is a possibility that times differ (and
there of course is) the question is not if it can happen but when.
Sure, but it doesn't matter one bit if the times between the primary and
secondary servers differ. If the timesta
On 6/14/2007, "Scott Marlowe" <[EMAIL PROTECTED]> wrote:
>
>[EMAIL PROTECTED] wrote:
>> I found a problem with my application which only occurs under high loads
>> (isn't that always the case?).
>>
>> snippets of perl...
>>
>> insert into tokens (token)
>> select values.token
>> from (values TOKE
On 6/14/2007, "Gregory Stark" <[EMAIL PROTECTED]> wrote:
>
>
><[EMAIL PROTECTED]> writes:
>
>> But everyone once in a long while it seems that I hit simultaneaous
>> execute() statements that deadlock on the insertion.
>
>What version of Postgres is this and do you have any foreign key constraint
On Thu, 14 Jun 2007, Marco Colombo wrote:
PG still has advantages vs. SQLite, being more featured (do you need
stored-procedures?). But if you plan to use an ORM tool for .Net you
might even be able to switch between SQLite and PostgreSQL at any time
w/o even noticing
The main thing to be wa
[EMAIL PROTECTED] wrote:
I found a problem with my application which only occurs under high loads
(isn't that always the case?).
snippets of perl...
insert into tokens (token)
select values.token
from (values TOKEN_LIST_STRING) as values(token)
left outer join tokens t using (token)
where t.tok
"hubert depesz lubaczewski" <[EMAIL PROTECTED]> writes:
> but in 8.3 i get:
> # select 1 where '1'::text in (1::int8);
> ERROR: operator does not exist: text = bigint
> why? i mean - i see that types are bad, but it worked in 8.2. why it was
> broken/modified in 8.3?
This is intentional --- impl
Johannes Konert schrieb:
> and time goes by, because everything seem to work just fine.
One should never rely on something seeming to be any kind of anything.
There are ways to _know_ things are right. Like using nagios to check
every vital sign state of your systems. That should include check
<[EMAIL PROTECTED]> writes:
> But everyone once in a long while it seems that I hit simultaneaous
> execute() statements that deadlock on the insertion.
What version of Postgres is this and do you have any foreign key constraints
or triggers on the table you're inserting into? Is that insert the
I found a problem with my application which only occurs under high loads
(isn't that always the case?).
snippets of perl...
insert into tokens (token)
select values.token
from (values TOKEN_LIST_STRING) as values(token)
left outer join tokens t using (token)
where t.token_idx is null
$sql =~ s/
On Jun 14, 2:18 pm, [EMAIL PROTECTED] (Michael Fuhr) wrote:
> On Thu, Jun 14, 2007 at 04:40:12AM -0700, [EMAIL PROTECTED] wrote:
> > I'd like to convert timestamps without timezone to unix epoch values
> > with at least microseconds resolution.
> > but when i do e.g.:
> > select extract (epoch from
Hrm, yet Bruce mentions it in one of his documents.
http://momjian.us/main/writings/pgsql/data_processing.pdf
It may be undocumented, but it get a lot of hits on google :)
why google? look to pgsql/src/backend/utils/adt/date.c :-)
Regards
Pavel Stehule
---(end of br
On Thu, Jun 14, 2007 at 01:54:09PM +0200, Pavel Stehule wrote:
> ># \df date_mi
>
> I know about it, but it's undocumented
Hrm, yet Bruce mentions it in one of his documents.
http://momjian.us/main/writings/pgsql/data_processing.pdf
It may be undocumented, but it get a lot of hits on google :)
On Thu, Jun 14, 2007 at 04:40:12AM -0700, [EMAIL PROTECTED] wrote:
> I'd like to convert timestamps without timezone to unix epoch values
> with at least microseconds resolution.
> but when i do e.g.:
> select extract (epoch from timestamp without time zone 'Thu 14 Jun
> 05:58:09.929994 2007');
>
On 06/14/07 02:24, PFC wrote:
The DELETE should block, no?
Why ?
Foreign keys put an ON DELETE trigger on the referenced table
Foreign keys that silently, automatic DELETE records?
Did I read that correctly?
Isn't that the point of ON DELETE CASCADE ?
Where'd that come from
2007/6/14, Martijn van Oosterhout <[EMAIL PROTECTED]>:
On Thu, Jun 14, 2007 at 09:00:12AM +0200, Pavel Stehule wrote:
> Hello
>
> PostgreSQL hasn't any official function for it. If you need it, you
> can write own function
Not true. Anything that is done by an operator as actually done by a
func
I'd like to convert timestamps without timezone to unix epoch values
with at least microseconds resolution.
but when i do e.g.:
select extract (epoch from timestamp without time zone 'Thu 14 Jun
05:58:09.929994 2007');
i get:
1181793489.92999
so i loose the last digit. I'd expect 1181793489.92999
On Wed, 2007-06-13 at 21:39 +0200, Frank Wittig wrote:
> But I would say one can
> rely on serial file names to increase steadily.
The whole of the PostgreSQL recovery system relies upon that, so yes we
can use that externally from the database also.
There's a patch to 8.3 for the restore_comman
On Thu, Jun 14, 2007 at 09:00:12AM +0200, Pavel Stehule wrote:
> Hello
>
> PostgreSQL hasn't any official function for it. If you need it, you
> can write own function
Not true. Anything that is done by an operator as actually done by a
function. So the function exists:
# \df date_mi
Gabriele wrote:
I'm going to develop a medium sized business desktop client server
application which will be deployed mostly on small sized networks and
later eventually, hopefully, on medium sized networks.
It will probably be developed using C#.
I do need a solid DBMS wich can work with .Net f
hi,
this query:
select 1 where '1'::text in (1::int8);
worked fine in 8.2:
# select version();
version
PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC
2007/6/14, guillermo arias <[EMAIL PROTECTED]>:
could you please give me an example?.
How could i make an inner join select with temporary tables?
This function does not work:
REATE OR REPLACE FUNCTION modelo.test2(OUT xart_cod character varying, OUT
xart_descri character varying)
RETURNS SE
Steve Manes wrote:
> I apologize if I'm having a rookie brain block, but is there a way to
> massage a string inside a proc to, for instance, strip it of all
> non-alpha characters using a regular expression?
SELECT regexp_replace(E'--> text\\\0120815_12 <--', '[^[:alpha:]]', '',
'g');
regexp_r
How can delete my suscription to this forums?
-Mensaje original-
De: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] En nombre de Jyoti Seth
Enviado el: jueves, 14 de junio de 2007 8:04
Para: 'Pavel Stehule'
CC: pgsql-general@postgresql.org; [EMAIL PROTECTED]
Asunto: Re: [SQL] setof or array
The DELETE should block, no?
Why ?
Foreign keys put an ON DELETE trigger on the referenced table
Foreign keys that silently, automatic DELETE records?
Did I read that correctly?
Isn't that the point of ON DELETE CASCADE ?
besides checking the referencing column on insert/
Hello all,
Is there any function to find differences in days between two dates?
I am using
select abs(current_date - '2007-06-15')
to get the desired result.
but I think there must be a function and I am missing it,
if so, can anybody please point me to that.
Thanks in advance
With regar
Hello
PostgreSQL hasn't any official function for it. If you need it, you
can write own function
CREATE FUNCTION date_diff(date, date) returns integer as $$
select $1-$2; $$ language sql;
Regards
Pavel Stehule
2007/6/14, Ashish Karalkar <[EMAIL PROTECTED]>:
Hello all,
Is there any function
76 matches
Mail list logo
