On Wed, Dec 15, 2004 at 12:19:14AM -0800, Eric Brown wrote:
I've got quite a few plpgsql functions that insert, update or delete.
They're all declared to return void. All other functions, I can just
run 'select f(...);' from psql to test them. I don't understand how to
test these ones that
I've got quite a few plpgsql functions that insert, update or delete.
They're all declared to return void. All other functions, I can just
run 'select f(...);' from psql to test them. I don't understand how to
test these ones that return void from psql. Thanks.
---(end
I'm trying to write a stored procedure in plpgsql that selects a row and possibly increments one of its fields. I thought I would do SELECT INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently plpgsql doesn't like the FOR UPDATE in a stored procedure. Does plpgsql automatically lock
Hi,
Citing Nadia Kunkov [EMAIL PROTECTED]:
Locally I have no problems connecting to the database and running psql.
I need to set up an ODBC connection from my Windows box.
Here is what I've done:
1.In postgresql.conf
tcpip_socket = true
(By the way does this eliminate
On Wed, 2004-12-15 at 01:38 -0800, Eric Brown wrote:
__
I'm trying to write a stored procedure in plpgsql that selects a row
and possibly increments one of its fields. I thought I would do SELECT
INTO my_record * FROM
I'm trying to write a stored procedure in plpgsql that selects a row
and possibly increments one of its fields. I thought I would do SELECT
INTO my_record * FROM my_table FOR UPDATE WHERE ..., but apparently
plpgsql doesn't like the FOR UPDATE in a stored procedure. Does plpgsql
automatically
Hi,
I've run into a situation (I should have forseen) and was hoping
someone could show me a way out.
I have a function that calls other functions. These other functions
are inserting rows and return the primary key for the inserted row.
Some of the tables are related in a way that they have
I remember reading someone's post recently, that provided directions
for using compiler directives to optimize Postgres for the G5 chip. If
that's you, could you please repost this, or send me a link, if it's on
a web site somewhere.
Thanks,
Doug
---(end of
On Tue, Dec 14, 2004 at 05:15:08PM -0800, Tim Vadnais wrote:
I tried to rebuild the two indexes and got the following error. The
optional FORCE has no effect on the output.
bwks=# reindex index pg_database_oid_index;
ERROR: shared index pg_database_oid_index can only be reindexed in
Thank you Tom, your suggestion was exactly what I needed.
Two tables in view "tpv" were being joined on a column with different
data types.
One was "text" and the other one was "varchar(10)". The 'old' system
did not complain.
The 'new' system does not allow this sloppyness on my part. When I
Hi.
I have a
CREATE TYPE ccnumber AS (
lengthSMALLINT,
tail VARCHAR(4),
encrypt TEXT
);
for credit card number storage. I would like this to be completely
transparent. However I can not get it to be a drop in replacement for a
text type, no matter what casts I create. I always
I'm running postgreSQL on SLES 8
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Dave Brazzeal wrote:
I'm running postgreSQL on SLES 8
No, there isn't. If we were able to write a repair utility, we might as
well fix the code to prevent the damage in the first place.
If you have a problem that you need to repair, please provide specifics.
--
Peter Eisentraut
On Mon, 13 Dec 2004 23:00:45 -0700 Michael Fuhr [EMAIL PROTECTED] wrote:
Do you mean that you don't need foreign key constraints to enforce
referential integrity, but only to cascade changes to another table?
If so, have you considered using triggers instead? Or have I
misunderstood what
Hello,
I am running an application on Postgresql 8.0.0 rc1. The application is
running on JBoss 4.0.1RC2 and I am using the JDBC driver
pg80b1.308.jdbc3.jar.
My problem is that insert that are sent to the database through a
prepared statement are not written in the database. The application is
Can a database notify all clients when something
changes and send the changes to the clients?
Let me explain.
Suppose that there are 3 workstations connected to a
central database. When Workstation A changes
something, Workstation B and C must get the changes.
So, can any database do that? If it
Satan Devil wrote:
Suppose that there are 3 workstations connected to a
central database. When Workstation A changes
something, Workstation B and C must get the changes.
Not really clear what you are trying to accomplish. The simple fact
that all 3 workstations are communicating with a
Allan,
Postgres is probably not the ideal solution to this problem. If you'd
like to try this though, two points:
- If the table really only has 20 rows, drop the index. If the table
really only has 20 active rows at a
time, then the planner will never use that index.
(run EXPLAIN on your
On Tue, 14 Dec 2004 15:38:45 -0600, Thomas F.O'Connell [EMAIL PROTECTED]
wrote:
[snip]
Would a more ideal setup be a six-drive server with a RAID 1+0 on four
drives with 2 drives dedicated to WAL that mirrored one another? Is
that overkill? Anyone have recommendations for an appropriate
Despite 30 minutes of googling, I am unable to find any debian
packages for Postgresql 8.0.0 RC1.
Does anyone know where I might obtain them?
Sw.
---(end of broadcast)---
TIP 8: explain analyze is your friend
How are bytea values encoded internally?
Or maybe a better question would be what is the proper way to access
bytea data from within a C function? Are there utility functions for
reading the bytea data as a stream of scalar values, for example?
--
Ron Peterson
Network Systems Manager
Mount
Hi All,
I have a table (table info) with 2 column, column 'name' and column 'quantity'.
name | quantity
--
a | 5
b | 3
a | 3
c | 4
b | 6
If I want to sum the sum of all entry in table.
name | quantity
On Wed, Dec 15, 2004 at 11:08:29PM -0500, Ron Peterson wrote:
How are bytea values encoded internally?
Or maybe a better question would be what is the proper way to access
bytea data from within a C function? Are there utility functions for
reading the bytea data as a stream of scalar
Frans [EMAIL PROTECTED] writes:
I try to use : select name, sum(quantity) from info where
sum(quantity)20 group by name;
This yields the message: 'Aggregates not allowed in WHERE clause'.
Can somebody help me here
See
http://www.postgresql.org/docs/7.4/static/tutorial-agg.html
On Wed, Dec 15, 2004 at 11:08:29PM -0500, Ron Peterson wrote:
How are bytea values encoded internally?
Or maybe a better question would be what is the proper way to access
bytea data from within a C function? Are there utility functions for
reading the bytea data as a stream of scalar
On Thu, Dec 16, 2004 at 12:02:34PM +0700, Frans wrote:
I try to use : select name, sum(quantity) from info where
sum(quantity)20 group by name;
This yields the message: 'Aggregates not allowed in WHERE clause'.
For aggregates use HAVING, not WHERE:
SELECT name, SUM(quantity)
FROM info
GROUP
Chris Smith wrote:
The easiest way to start is to turn on query logs for your server and
see if it gets that far.
When turning on query logs in Postgresql, I only get the content of the
prepared statement without the parameters (question mark). I there a way
to expand what is executed by the
Eric Brown wrote:
I've got quite a few plpgsql functions that insert, update or delete.
They're all declared to return void. All other functions, I can just run
'select f(...);' from psql to test them. I don't understand how to test
these ones that return void from psql.
neilc=# create function
Thanks. I forgot the 'return;' and the error message led me in the
wrong direction. Thanks!
On Dec 15, 2004, at 12:43 AM, Neil Conway wrote:
Eric Brown wrote:
I've got quite a few plpgsql functions that insert, update or delete.
They're all declared to return void. All other functions, I can
Alain M. Gaudrault wrote:
I've come across a problem which is only happening intermittently, and
having a difficult time finding information to help me determine what is
the source of my frustration.
The symptom is the error reported in the subject. We invoke SQL
statements from a Java
Eric Brown wrote:
I've got quite a few plpgsql functions that insert, update or delete.
They're all declared to return void. All other functions, I can just run
'select f(...);' from psql to test them. I don't understand how to test
these ones that return void from psql. Thanks.
I always return
I've got a few plpgsql stored functions (in 7.4.x) that use the
select x into y from table where condition for update
syntax without any problem.
Maybe there's something else going on?
John Sidney-Woollett
Eric Brown wrote:
I'm trying to write a stored procedure in plpgsql that selects a row and
On Wed, Dec 15, 2004 at 10:22:31 +1100,
Harvey, Allan AC [EMAIL PROTECTED] wrote:
Hi all,
I'm after suggestions on how to keep the initial performance
of a system I've put together for longer than 2 minutes.
I have a small table about 20 rows, a constant, that is receiving
about 160
On Tue, 2004-12-14 at 18:43, Dann Corbit wrote:
Would it be possible to rebuild all non-btree indexes when a recovery
takes place?
Considering how long I've seen some large hash indexes take to build,
that might be better left as an optional setting.
Another thing that seems it might be nice
I have never tested a particular scenario like this out, but would AFTER
INSERT triggers resolve this issue for you?
On Wednesday 15 December 2004 11:53 am, Larry White saith:
Hi,
I've run into a situation (I should have forseen) and was hoping
someone could show me a way out.
I have a
On Wed, Dec 15, 2004 at 11:53:55AM -0500, Larry White wrote:
I have a function that calls other functions. These other functions
are inserting rows and return the primary key for the inserted row.
Some of the tables are related in a way that they have a foreign key
reference to a table
My mistake. There was something else going on and I misinterpreted
the cause of the problem. The foreign key references are valid within
the transaction even though the initial updates are not yet committed.
apologies for the distraction and thanks again for your help.
On Wed, 15 Dec 2004
Maybe this could be handled by logging the fact that the index is being
modified. Then during recovery, if an index was being modified, and
the log doesn't indicate that the modification was completed, the index
can be rebuilt?
On Dec 15, 2004, at 10:02 AM, Scott Marlowe wrote:
On Tue,
No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this
is a question that comes up pretty often. I think the bulk of the work
to make this happen could be done outside of the core database, so it
seems a good candidate for pgfoundry.
On Sat, Dec 11, 2004 at 07:44:03AM +0100,
I'm posting this here for search-engine's sake, so future people
having this same problem can find the solution here.
After installing PostgreSQL from FreeBSD's ports, and running su -
pgsql -c initdb for the first time, I got this common error:
could not create semaphores : No space left on
I'd like to UNION two queries but the distinct-criteria for UNION should
not be all columns in the queries, but only one.
example. two tables:
test=# select id,name from t1;
id | name
+--
1 | bob
2 | mike
(2 rows)
test=# select id,name from t2;
id | name
+-
1 | bob
Miles Keaton [EMAIL PROTECTED] writes:
So --- I think if you install PostgreSQL from FreeBSD ports, and start
the server, and THEN run initdb, it will give you this error.
What this says is that you're running PG with settings that use up more
than half of the kernel's default limit on the
[EMAIL PROTECTED] (Martijn van Oosterhout) writes:
On Tue, Dec 14, 2004 at 05:15:08PM -0800, Tim Vadnais wrote:
I tried to rebuild the two indexes and got the following error. The
optional FORCE has no effect on the output.
bwks=# reindex index pg_database_oid_index;
ERROR: shared index
You probably want something more like:
SELECT DISTINCT ON (id), * FROM
( subquery1
UNION ALL
subquey2
);
The fact that UNION sorts at all is a side-effect of the
implementation. The distinct part is part of the SQL spec. Use UNION
ALL to get all the rows and then DISTINCT ON to do what
The easiest way to start is to turn on query logs for your server and
see if it gets that far.
If it does - copy the query into psql and run it manually - it will tell
you if there's a problem.
If it doesn't get that far, then that's another problem altogether...
Regards,
Chris Smith
Suite 30,
On Wed, Dec 15, 2004 at 03:37:05PM -0800, Satan Devil wrote:
Can a database notify all clients when something
changes and send the changes to the clients?
Sure. Use LISTEN and NOTIFY.
--
Alvaro Herrera ([EMAIL PROTECTED])
Al principio era UNIX, y UNIX habló y dijo: Hello world\n.
No dijo
A long time ago, in a galaxy far, far away, [EMAIL PROTECTED] (Frans) wrote:
This yields the message: 'Aggregates not allowed in WHERE clause'.
Can somebody help me here
What it says is right; SQL does not permit using aggregates in the
WHERE clause.
You need to look at the HAVING clause
47 matches
Mail list logo
