2007/6/14, Jyoti Seth [EMAIL PROTECTED]:
Thanks for the solution. With this I am able to pass arrays and
multidimensional arrays in postgresql functions.
One of my problem is still left I want to pass set of values with different
datatypes.For eg:
I want to pass following values to the
Thanks a lot.
Regards,
Jyoti
-Original Message-
From: Pavel Stehule [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 14, 2007 11:27 AM
To: Jyoti Seth
Cc: pgsql-general@postgresql.org; [EMAIL PROTECTED]
Subject: Re: [SQL] setof or array as input parameter to postgresql 8.2
functions
On 6/13/07, Ben [EMAIL PROTECTED] wrote:
Why would that be a problem if each is configured to listen on different
addresses?
But maybe a better question to ask would be how people are doing failover
in the case where you have two servers, each handling a seperate set of
data and acting as
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
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
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
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
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');
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
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
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
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
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_command
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
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
function.
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
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');
i
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 :)
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
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
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 =~
[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
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
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 --- implicit
[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
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
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 constraints
or triggers
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 TOKEN_LIST_STRING) as
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
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 Postgres is this and do you
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
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
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
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
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 and
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?
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
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
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 execute
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.576
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
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.
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
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.
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 TM
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)
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=
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
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 in
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 other
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 got
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,
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
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 table
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
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
raising it
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
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?
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
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?
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
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
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
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
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
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 fault
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.
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:
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
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
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
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.
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 Lenny
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
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 allowed ?
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 think
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
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 over
78 matches
Mail list logo
