Hi,
I have a table named mydata
CREATE TABLE public.mydata (
id int4 DEFAULT nextval('public.mydata_id_seq'::text) NOT NULL,
name varchar(50)
) WITH OIDS;
and I have a function as follows
CREATE OR REPLACE FUNCTION insertmydata(varchar) RETURNS int
as '
declare
new_id integer;
begin
IN
Thanks Patrick and Bruno for your replies,
The auto-added "FROM" feature is pretty slick for enabling JOINs within
a DELETE. Allowing this to be explicit in 8.1 is going to be even
better.
Since DELETEing from pg_users is an unsupported way to remove users, I
am going to use the procedure in the
Venki wrote:
Hi,
I have a table named mydata
CREATE TABLE public.mydata (
id int4 DEFAULT nextval('public.mydata_id_seq'::text) NOT NULL,
name varchar(50)
) WITH OIDS;
and I have a function as follows
CREATE OR REPLACE FUNCTION insertmydata(varchar) RETURNS int
as '
declare
new_id integer;
b
On Thu, Sep 01, 2005 at 11:58:27AM +0530, Venki wrote:
>
> new_id = EXECUTE("SELECT FROM currval("mydata_id_seq")");
The above line has quoting problems and an erroneous SELECT query,
and it wouldn't work anyway because that's not the way to retrieve
results from EXECUTE. Aside from that, EXECU
Art Fore wrote:
I am trying to start postgresql via webmin, but not having success. I
get a message in the syslog "su:(to postgres) root on none"
This is suse 9.3. Could someone tell what that message means?
It just means something is switching to user=postgres from user=root.
Nothing too un
Hello.
I'm getting an Out Of Memory error when I try to create a GiST index on
a geometry column (PostGIS) with approximately 33,000,000 rows. I can
truncate the data in the table, create the GiST index on the empty
table, but when I try to reload the data via pg_restore I get the
error. I ge
Peter Wilson wrote:
Joshua D. Drake wrote:
I've just re-written our Whitebeam code to drop large-objects in
favour of BYTEA fields.
All the old problems of large objects in backups exist, but the
killer for us was that none of the current replication systems, at
least that I could find,
James Cradock wrote:
Hello.
I'm getting an Out Of Memory error when I try to create a GiST index on
a geometry column (PostGIS) with approximately 33,000,000 rows. I can
truncate the data in the table, create the GiST index on the empty
table, but when I try to reload the data via pg_restore
Thanks.
I'm using PostgreSQL 8.0.2.
On Sep 1, 2005, at 6:35 AM, Richard Huxton wrote:
James Cradock wrote:
Hello.
I'm getting an Out Of Memory error when I try to create a GiST index
on a geometry column (PostGIS) with approximately 33,000,000 rows. I
can truncate the data in the table, cre
On Thu, 1 Sep 2005, James Cradock wrote:
Hello.
I'm getting an Out Of Memory error when I try to create a GiST index on a
geometry column (PostGIS) with approximately 33,000,000 rows. I can truncate
the data in the table, create the GiST index on the empty table, but when I
try to reload the
Dear Mr. Richard Huxton & Michael Fuhr,
Thanks for your prompt replies, it has helped me a lot. The problem was solved by using this line
SELECT INTO new_id currval(''mydata_id_seq'');
Thanks again for your valuable suggestions
Regards,
venki
---Original Message---
From:
On Wed, 2005-08-31 at 00:50 -0700, Matthew Peter wrote:
> Hmmm. I was thinking of a more comprehensive solution
> or document resource. I would like to know what does
> what. Why tweak that or why not to ya know?
Matt,
I've found the annotated postgresql.conf references on this page (as
well as r
[Please copy the mailing list on replies so others can contribute
to and learn from the discussion. Also, please don't top-post, as
it destroys the flow of the discussion; I've moved your questions
to a more logical place.]
On Wed, Aug 31, 2005 at 03:49:57PM -0600, [EMAIL PROTECTED] wrote:
> On W
Hallo,
in an existing application I got an exception and tracked it down to a
generated query from the Java OR mapping solution Hibernate
Actually I was suprised that the query does not work and created a
simple use case to explain the problem (see and of this email).
The postgre SQL exception i
If I want to retrieve all columns from a table, is there any disadvantage by
using select *
instead of listing all the columns ? Will select * cause overhead, more
times to run ?
Thanks !
Tan
---(end of broadcast)---
TIP 1: if posting/reading thro
Michael Fuhr wrote:
> [Please copy the mailing list on replies so others can
> contribute to and learn from the discussion. Also, please
> don't top-post, as it destroys the flow of the discussion;
> I've moved your questions to a more logical place.]
>
> On Wed, Aug 31, 2005 at 03:49:57PM -0600,
"Tan Chen Yee" <[EMAIL PROTECTED]> writes:
> If I want to retrieve all columns from a table, is there any disadvantage by
> using select *
> instead of listing all the columns ? Will select * cause overhead, more
> times to run ?
No, it shouldn't make any difference there. The disadvantage of
SE
The first question is do you really need all the columns. Most times
you don't need them. There will be network overhead for sure in
returning all columns instead of just the few that you want. Also
select * is not very clear with what is going on in the statement.
Where I work we have standards t
max_connections = 50 on my server
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Hi All
I'm trying to create a aggregate function similar 9but not identical) to
mysql's group_concat.
What I want to be able to do is pass and order by field to the aggregate so
I can be certain I get the list of strings in the correct order.
Does anyone have any ideas how this could be done? In
Hi Frank;
I will use your questions to help in the next version of my migration
guide, but you might want to take a look at it anyway.
You can find it at:
http://www.metatrontech.com/wpapers/
It covers many areas of incompatibility between MySQL and PostgreSQL.
My own suggestions is that yo
Hello
I have next problem: I have Windows 2000 s. Postgresql 8.0 database
have 300MB and hardware configuration (2 x P3 1Ghz,1GB RAM and SCSI
HD).
How set the shared_buffers and other parameters to better performance.
for example:
When i read table from workstation (20.000 record and 20 column)
Hi,
I recently shut down my machine (as far as I know, it went down
cleanly). When I brought it back up, one of my databases was messed
up. Here is the error I get when I try to select:
ERROR: could not access status of transaction 4114057
DETAIL: could not open file "/var/lib/postgres/da
I'm getting the following error when attempting to use my application:
ERROR: current transaction is aborted, commands ignored until end of
transaction block
I have no clue... the only idea I have is to somehow release any
transaction locks, but I don't how to list, or even if possible, to list
Sebastian Hennebrueder <[EMAIL PROTECTED]> writes:
> -- the following query has a inner join and an implicit join and does
> not work.
> select a.name as act_name,
> u.name as trainer
> from
> activity a,
> localgroup lg,
> sponsor spon
> inner join tuser u on a.fk_trainer=u.id
> where spon.name='J
On Thu, Sep 01, 2005 at 10:04:12AM +0100, Charlotte Pollock wrote:
> Hi All
>
> I'm trying to create a aggregate function similar 9but not identical) to
> mysql's group_concat.
>
> What I want to be able to do is pass and order by field to the aggregate so
> I can be certain I get the list of str
Should something similar to the following be possible in PG 8.0.3?
create type foo_t as ( c1 int, c2 int );
create table tab (
name varchar not null,
foos foo_t[]
);
The response I get is:
ERROR: type "foo_t[]" does not exist
The create type documentation says that postgres silently create
"Casey T. Deccio" <[EMAIL PROTECTED]> writes:
> I recently shut down my machine (as far as I know, it went down
> cleanly). When I brought it back up, one of my databases was messed
> up. Here is the error I get when I try to select:
> ERROR: could not access status of transaction 4114057
> D
Kelly Burkhart <[EMAIL PROTECTED]> writes:
> The create type documentation says that postgres silently creates an
> array type for each base type with an underscore prepended to the base
> name.
"Base type" in this context specifically means "not composite type".
We may support that someday, but i
Hi list,
we are using schema's to separate different administrations, and are using
Zope/Plone as a front-end portal to the database.
In this installation almost the first statement is:
cvix=# SELECT set_config('search_path', '"' || t2.schema || '"', true)
cvix-# FROM "Lindix"."Gebruikers" as
On Thu, 2005-09-01 at 10:36, Jonathan Villa wrote:
> I'm getting the following error when attempting to use my application:
>
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block
>
> I have no clue... the only idea I have is to somehow release any
> transacti
Charlotte Pollock <[EMAIL PROTECTED]> writes:
> I'm trying to create a aggregate function similar 9but not identical) to
> mysql's group_concat.
> What I want to be able to do is pass and order by field to the aggregate so
> I can be certain I get the list of strings in the correct order.
The way
Thanks for the info. I was trying the old .conf files from a database
trasfered from 7.54. The tcpip enable is different between it and 8.03.
Got it working now with pgadminIII.
Art
Richard Huxton wrote:
Art Fore wrote:
I am trying to start postgresql via webmin, but not having success. I
g
Jonathan Villa wrote:
I'm getting the following error when attempting to use my application:
ERROR: current transaction is aborted, commands ignored until end of
transaction block
I have no clue...
An error has occurred on a previous command. As a result, this
transaction is aborted and you
On Thu, Sep 01, 2005 at 05:14:41PM +0200, Martijn van Oosterhout wrote:
> On Thu, Sep 01, 2005 at 10:04:12AM +0100, Charlotte Pollock wrote:
> > What I want to be able to do is pass and order by field to the aggregate so
> > I can be certain I get the list of strings in the correct order.
>
> Orde
On Thu, Sep 01, 2005 at 10:36:07AM -0500, Jonathan Villa wrote:
> I'm getting the following error when attempting to use my application:
>
> ERROR: current transaction is aborted, commands ignored until end of
> transaction block
Some earlier command failed so the entire transaction (or subtransa
Richard
Sorry for the delay.
question, the select * from pg_stat_activity
thows
datid|datname|procpid|usesysid|usename|current_query|query_start
52800|"sume"|30124|1|"postgres"|""|"2005-09-01
13:30:02.921844-03"
52800|"sume"|30125|1|"postgres"|""|"2005-09-01
13:37:21.631802-03"
52800|"
--- Richard Huxton escribió:
> marcelo Cortez wrote:
> > Hi Richard , folks
> >
> > the query is:
> >
> > SELECT c.actuacion_car AS c_actuacion,
> > c.comentario1 || ' ' || c.comentario2 || ' ' ||
> > c.comentario3 AS c_comentario
> > FROM caratult AS c INNER JOIN extractt AS t1 ON
> ...et
On Thu, Sep 01, 2005 at 06:13:13PM +0200, Dick Kniep wrote:
>
> cvix=# SELECT set_config('search_path', '"' || t2.schema || '"', true)
> cvix-# FROM "Lindix"."Gebruikers" as t1, "Lindix"."Administratie" as t2
> cvix-# WHERE uid = 'zon0023'
> cvix-# AND t1.administratie_id = t2.administratie_id;
>
"Casey T. Deccio" <[EMAIL PROTECTED]> writes:
> On Thu, 2005-09-01 at 11:59 -0400, Tom Lane wrote:
>> Define "didn't work" ... what happened, exactly, when you tried it?
> Sorry, I was too tired last night to try it again and post the error.
> Here it is:
> ERROR: could not access status of tran
Tom Lane schrieb:
>Sebastian Hennebrueder <[EMAIL PROTECTED]> writes:
>
>
>>-- the following query has a inner join and an implicit join and does
>>not work.
>>select a.name as act_name,
>>u.name as trainer
>>from
>>activity a,
>>localgroup lg,
>>sponsor spon
>>inner join tuser u on a.fk_trainer
On Thu, 2005-09-01 at 11:59 -0400, Tom Lane wrote:
> "Casey T. Deccio" <[EMAIL PROTECTED]> writes:
> > I recently shut down my machine (as far as I know, it went down
> > cleanly). When I brought it back up, one of my databases was messed
> > up. Here is the error I get when I try to select:
>
On Thu, 2005-09-01 at 13:11 -0400, Tom Lane wrote:
> > How would I create a dummy clog segment file, if needed?
>
> What you'll have to do is pad out the segment file to full size (256K)
> by appending zeroes. (dd from /dev/zero is useful for this.)
>
It worked! Thanks for your help!
Casey
Hi,
I was just wondering if postgresql supports queries between servers in
order to pull info from different machines.
Let's say server A can run a query against server B... something like:
server1 running a SELECT * FROM server2.table
or joins
SELECT A.name, B.name FROM server1.table A, serve
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Thu, Sep 01, 2005 at 05:14:41PM +0200, Martijn van Oosterhout wrote:
>> SELECT aggregate(field) FROM (SELECT field FROM xxx ORDER BY wherever) x;
> I've occasionally relied on this but I've never been completely
> comfortable with it. Is there any gua
>>mysqldump --extended-insert=FALSE<<
Thanks for the help, this is allowing me to import the data now.
Regards,
Frank
At 09:37 AM 9/1/2005, Michael Fuhr wrote:
[Please copy the mailing list on replies so others can contribute
to and learn from the discussion. Also, please don't top-post, as
On Thu, 2005-09-01 at 13:34, Federico Balbi wrote:
> Hi,
> I was just wondering if postgresql supports queries between servers in
> order to pull info from different machines.
> Let's say server A can run a query against server B... something like:
>
> server1 running a SELECT * FROM server2.tab
On Thu, Sep 01, 2005 at 01:34:41PM -0500, Federico Balbi wrote:
> I was just wondering if postgresql supports queries between servers in
> order to pull info from different machines.
Not natively, but contrib/dblink provides a way to query other
PostgreSQL databases, which can be running on othe
We have an old legacy app that connects to our PostgreSQL (7.4.6)
database. It is an old Visual Basic/Business Objects (VB 6.0, MDAC
2.5) program that selects a group of records and locks them by
setting a field to an "in-progress" status. The user then works on
those records and when done, ret
I'm wondering if it's possible for the ordinary user to check if SELECT
is granted on a give table, without doing the select ?
I want to use it in an application with a menu structure, where I only
want to enable those options where SELECT on a the tables used in the
given menu is granted.
Inst
> Is that select * being used to COUNT the number of rows? If so,
> then do a "select count(*)" which will take the db engine about as
> long, but it won't need to transfer the data across.
Beats me. WE are not requesting a "select *" at all in the VB code. We
are selecting and updating the rows
Hello, I've been reading a little the ECPG
(Embedded SQL in C) and the doc is (I guess) very clear, but I cannot find any
examples in the documentation, any idea where to get examples? inside the pgsql
source code?
Thanks a lot!
Title: Message
see
the contrib directory,,, i think there is also a simple example in the
docs...
reid
-Original Message-From: Cristian Prieto
[mailto:[EMAIL PROTECTED] Sent: Thursday, September 01,
2005 4:10 PMTo: pgsql-general@postgresql.orgSubject:
[GENERAL] ECPG
Title: Message
http://tutorials.findtutorials.com/read/id/185/headline/Professional+Linux+Programming+Part+3+-+ECPG
reid
-Original Message-From: Cristian Prieto
[mailto:[EMAIL PROTECTED] Sent: Thursday, September 01,
2005 4:10 PMTo: pgsql-general@postgresql.orgSubject:
=?UTF-8?B?UG91bCBNw7hsbGVyIEhhbnNlbg==?= <[EMAIL PROTECTED]> writes:
> I'm wondering if it's possible for the ordinary user to check if SELECT
> is granted on a give table, without doing the select ?
See the has_table_privilege() function.
regards, tom lane
--
Title: Message
Thanks a lot! It was very helpfull!!!
- Original Message -
From:
Reid
Thompson
To: Cristian Prieto ; pgsql-general@postgresql.org
Sent: Thursday, September 01, 2005 2:43
PM
Subject: Re: [GENERAL] ECPG
examples...
http://tutorials.findtut
CSN wrote:
> Perhaps another possible feature request! I've looked
> through the docs and it doesn't appear that it's
> possible to create deferred triggers - i.e. they don't
> get called unless the current transaction commits.
The semantics of such a thing appear to be indeterminate. What happe
Steve Crawford <[EMAIL PROTECTED]> writes:
> Somewhere in the black box that is VB/BusinessObjects/ODBC something
> is deciding that a "select *" is necessary for reasons unknown and
> then choking on (well, not actually choking but digesting slowly) the
> data returned.
> I'm just trying to fi
On Thu, 2005-09-01 at 15:21, Steve Crawford wrote:
> > Is that select * being used to COUNT the number of rows? If so,
> > then do a "select count(*)" which will take the db engine about as
> > long, but it won't need to transfer the data across.
>
> Beats me. WE are not requesting a "select *" a
[redirected from -patches]
On Wed, 2005-08-03 at 16:25 -0400, Tom Lane wrote:
> Matt Miller <[EMAIL PROTECTED]> writes:
> > allow a PL/pgSQL exception to not automatically rollback
> > the work done by the current block.
>
> This fundamentally breaks the entire backend. You do not have the
> opt
[Please copy the mailing list on replies so others can contribute
to and learn from the discussion. I've quoted more of your message
than I ordinarily would because other people won't have seen it and
they won't find it in the list archives.]
On Thu, Sep 01, 2005 at 11:35:43PM +0200, Dick Kniep w
hi,
I have this preformance question.
create view test_v as select 'text'::varchar as Field1, 'text'::varchar as Field2;
create or replace function test() returns setof test_v as $$
declare
res test_v%ROWTYPE;
begin
for res in
select t1.field1, t1.field2 from table1 t1;
loop
return
On Thu, 2005-09-01 at 13:58, Steve Crawford wrote:
> We have an old legacy app that connects to our PostgreSQL (7.4.6)
> database. It is an old Visual Basic/Business Objects (VB 6.0, MDAC
> 2.5) program that selects a group of records and locks them by
> setting a field to an "in-progress" statu
vishal saberwal <[EMAIL PROTECTED]> writes:
> The query I am actually trying to optimize is long and has a few joins (for=
> =20
> normalization) and hence didn't copy it here.
> The function structure is similar to the one above.
> (a) Am i right in thinking that if I eliminate the for loop, some
Hello, I've been working just a little with SPI in
a few stored functions, this is a model of my SP:
PG_FUNCTION_INFO_V1(myspi);
Datum
myspi(PG_FUNCTION_ARGS)
{
bool isnull;
bytea *val;
Oid *karg;
void *plan;
int res;
ret =
SPI_connect();
karg = (Oid *)
pallo
On Thu, Sep 01, 2005 at 08:23:31PM -0600, Cristian Prieto wrote:
> Hello, I've been working just a little with SPI in a few stored
> functions, this is a model of my SP:
Please post a real example instead of a "model." The code you
posted fails to compile, with errors and warnings like the follow
same size VARCHAR or INT IX faster? i assume INT. The
reason I ask is I was wondering what (if any) is the
avg delay from one over the other? And benefit of one
over the other? Thanks.
__
Yahoo! Mail
Stay connected, organized, and protected. Take
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Thu, Sep 01, 2005 at 08:23:31PM -0600, Cristian Prieto wrote:
>> Hello, I've been working just a little with SPI in a few stored
>> functions, this is a model of my SP:
> Please post a real example instead of a "model."
Also, it's good to make at leas
68 matches
Mail list logo
