By referring to article at :
http://blogs.msdn.com/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx
I try to implement as follow :
CREATE OR REPLACE FUNCTION update_or_insert_statistic(int, text, text, double
precision)
RETURNS void AS
$BODY$DECLARE
_lotID ALIAS FOR $1;
_
Naoko Reeves writes:
> We are using custom plugin to connect to postgresql.
> We reviewed OS (OS X 10.6.2 2.26 GHz Quad-Core Intel Xeon Meomory 6
> GB) system log and noticed that the following lines are repeated in
> the log all day...(This log records NOTICE from sql as well)
> Feb 17 20:43:25
version: "PostgreSQL 8.3.8 on i386-apple-darwin8.11.1, compiled by GCC
i686-apple-darwin8-gcc-4.0.1 (GCC) 4.0.1 (Apple Computer, Inc. build
5370)"
We are using custom plugin to connect to postgresql.
We reviewed OS (OS X 10.6.2 2.26 GHz Quad-Core Intel Xeon Meomory 6
GB) system log and noticed tha
Lew writes:
> Eric B. Ridge wrote:
>> That explains it. Thanks. Breaks the rule of least surprise, but it is SQL.
> I don't think it does break the rule of least surprise. How would one
> expect the column or the alias to have precedence without knowledge of
> the rule from documentation? The
Scott Bailey wrote:
SQL name resolution rules are that column names
have higher precedence than aliases and variables.
So it will always bind to the column not the alias.
Eric B. Ridge wrote:
That explains it. Thanks. Breaks the rule of least surprise, but it is SQL.
I don't think it doe
Hi,
> Even in Oracle, I don't believe rowid bypasses
> indexes, its more like an implicit SERIAL PRIMARY KEY field.
Well, I understand the point is not very relevant, since oid is not
similar to rowid. In Oracle, index scans are bypassed if we use rowid.
1)Access by unique index
SQL> select *
Jayadevan M wrote:
Hi,
> The primary question that needs to be asked is what do you want to
do with them?
> It is not so much a performance issue as an admin issue. OIDs where
created for
> Postgres internal system use and leaked out to user space. As a
result they
> have some shortcomings as
On Thu, Feb 18, 2010 at 8:46 PM, Jayadevan M
wrote:
>
> Hi,
> > The primary question that needs to be asked is what do you want to do with
> > them?
> > It is not so much a performance issue as an admin issue. OIDs where created
> > for
> > Postgres internal system use and leaked out to user spa
Hi,
> The primary question that needs to be asked is what do you want to do
with them?
> It is not so much a performance issue as an admin issue. OIDs where
created for
> Postgres internal system use and leaked out to user space. As a result
they
> have some shortcomings as detailed in the ab
The last version of pgpool(not pgpool-II) had been released almost 3
years ago. So I guess it has many bugs found during this 3 years.
However as long as the bug does not bite you, it's ok.
I just recommend to use pgpool-II in the real world use.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http:/
On Feb 18, 2010, at 5:52 PM, Scott Bailey wrote:
> SQL name resolution rules are that column names have higher precedence than
> aliases and variables. So it will always bind to the column not the alias.
That explains it. Thanks. Breaks the rule of least surprise, but it is SQL.
eric
--
Sent
Eric B. Ridge wrote:
On Feb 18, 2010, at 4:31 PM, Scott Bailey wrote:
I'm not sure why you would be surprised by that behavior. You are grouping by a
timestamp, so any microsecond difference will be a new group.
I get that. ;) Hence the ::date. This is what doesn't make sense:
Expected:
Peter Geoghegan escribió:
> Hello,
>
> I maintain an app where database users correspond to actual users,
> with privileges granted or denied to each. At the moment, records that
> each user creates are identified as such by a text column that has a
> default value of session_user(). I don't need
On Feb 18, 2010, at 4:31 PM, Scott Bailey wrote:
> I'm not sure why you would be surprised by that behavior. You are grouping by
> a timestamp, so any microsecond difference will be a new group.
I get that. ;) Hence the ::date. This is what doesn't make sense:
Expected: select day::date as
On Thu, 2010-02-18 at 09:33 -0600, Little, Douglas wrote:
> psql p1gp1 <>$LOGFile 2>&1
>
> \set ON_ERROR_STOP
>
> select da_test.QATestBuild(false)
>
> QUIT
mod to your needs...
$ cat dummy.sql
#MYTESTID=`psql -t -c "select da_test.QATestBuild(false)" dbname`
MYTS=`psql -t -c "select to
Eric B. Ridge wrote:
Maybe I'm getting too old to SQL anymore, but I ran across something yesterday in a machine generated query that took me over an hour to figure out.
Here's a little testcase. Maybe somebody can explain why the last "Not
Expected" case does what it does.
select version()
Maybe I'm getting too old to SQL anymore, but I ran across something yesterday
in a machine generated query that took me over an hour to figure out.
Here's a little testcase. Maybe somebody can explain why the last "Not
Expected" case does what it does.
select version();
PostgreSQL 8.4.1 on
Thanks Alvaro,
Hopefully it will stop my locking issue when I have high volume of changes on
this table and vacuum starts.
Thx
Chris :)
> Date: Thu, 18 Feb 2010 16:55:24 -0300
> From: alvhe...@commandprompt.com
> To: compuguruchrisbar...@hotmail.com
> CC: schmi...@gmail.com; d...@
Chris Barnes escribió:
>
>
>
> Right you are, I'm due to upgrade end of month on this system.
>
> Here I was thinking 8.4. Sorry for the spam.
You can "INSERT INTO pg_autovacuum VALUES ('schema.table'::regclass,
false, -1, -1, ...);" in previous versions.
--
Alvaro Herrera
On 18/02/2010 12:05, wilcza...@op.pl wrote:
>
> CREATE OR REPLACE FUNCTION A3() RETURNS VOID AS $BODY$
> begin
> perform A1();
> end;
> $BODY$ LANGUAGE 'plpgsql';
You need to do:
select * from A1();
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
r...@iol.ie
--
Sent via pgsql-general mai
Hello
2010/2/18 :
> I have a function A1 that returns setof records, and I use it in two ways:
> 1) from function A2, where I need results from A1
> 2) from function A3, where I don't need these results, all I need is to
> execute logic from A1
>
> Here ale very simple versions of my functions:
>
I have a function A1 that returns setof records, and I use it in two ways:
1) from function A2, where I need results from A1
2) from function A3, where I don't need these results, all I need is to
execute logic from A1
Here ale very simple versions of my functions:
CREATE OR REPLACE FUNCTION A1()
Right you are, I'm due to upgrade end of month on this system.
Here I was thinking 8.4. Sorry for the spam.
Chris
[postg...@pgprd01:~/pgcheck]$ psql
Welcome to psql 8.3.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL comm
On Thu, Feb 18, 2010 at 12:37 PM, Richard Huxton wrote:
> On 18/02/10 17:20, Chris Barnes wrote:
>
>>
>> I'm trying to have this table ignored by the autovacuum process.
>>
>> It wasn't created with this in mind, hoping there is still a way?
>>
>
> alter table schema.table SET (autovacuum_enable
On 18/02/10 17:20, Chris Barnes wrote:
I'm trying to have this table ignored by the autovacuum process.
It wasn't created with this in mind, hoping there is still a way?
alter table schema.table SET (autovacuum_enabled = false);
ERROR: unrecognized parameter "autovacuum_enabled"
Close, b
Hi,
I'm trying to have this table ignored by the autovacuum process.
It wasn't created with this in mind, hoping there is still a way?
Thanks,
Chris
alter table schema.table SET (autovacuum_enabled = false);
ERROR: unrecognized parameter "autovacuum_enabled"
Thanks a lot so far, got it working with the cast. Probably the documentation
about create type where the type modifiers are described should be extended in
order to find that.
Thanks and regards
Carsten Kropf
Am 18.02.2010 um 16:46 schrieb Tom Lane:
> Yeb Havinga writes:
>> Carsten Kro
On Thu, Feb 18, 2010 at 10:33 AM, Little, Douglas wrote:
> psql
>
> orbitz=# \!testvar=1234
>
> orbitz=# \!export testvar
>
> orbitz=# \!echo $testvar
>
> 1234
>
> orbitz=# \q
>
> -bash-3.00$ echo
> $testvar
>
>
> 1234
>
What shell are you using that allows a child process to alter the parent
pro
Yeb Havinga writes:
> Carsten Kropf wrote:
>> I wanted to add a new type that supports modifiers (comparable to
>> numeric/varchar).
> You need to add a cast from the type to itself, e.g.
The CREATE CAST reference page has the gory details here.
regards, tom lane
--
Se
Ivan Sergio Borgonovo writes:
> How am I supposed to output multibyte strings in an errmsg (and Co.)
> as in
> errmsg("operator not permitted '%s'", mbstring)
As long as it's in the current database encoding, you just do it,
just like that.
regards, tom lane
--
Sent vi
Hello,
I want to get a sql select output into a psql variable. Any ideas how I might
need to do this.
My script executes a function which returns a TESTID. I'd like to imbed the
testid in the script output filenames.
I see that psql can set environment variables with the psql \i command.
But
On Wednesday 17 February 2010 8:13:51 pm Jayadevan M wrote:
> Hi,
> I was reading about oid and default configuration of PostgreSQL. A couple
> of doubts
> 1) Why is use of OIDS considered deprecated? Is there something else that
> can be used in place of oids for user tables?
Sequences:
http://w
How am I supposed to output multibyte strings in an errmsg (and Co.)
as in
errmsg("operator not permitted '%s'", mbstring)
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
ht
Thanks. I will do testing.
On Thu, Feb 18, 2010 at 4:29 PM, Richard Huxton wrote:
> On 18/02/10 10:54, dipti shah wrote:
>
>> Okay then I think below works:
>>
>>1. Revoke permission ALL permissions from PUBLIC on schema.
>>
>> REVOKE ALL ON ALL TABLES IN SCHEMA mySchema FROM P
On 18/02/10 10:54, dipti shah wrote:
Okay then I think below works:
1. Revoke permission ALL permissions from PUBLIC on schema.
REVOKE ALL ON ALL TABLES IN SCHEMA mySchema FROM PUBLIC;
2. Give store procedure for creating table with SECURITY DEFINER marked
so that all ta
Okay then I think below works:
1. Revoke permission ALL permissions from PUBLIC on schema.
REVOKE ALL ON ALL TABLES IN SCHEMA mySchema FROM PUBLIC;
2. Give store procedure for creating table with SECURITY DEFINER marked
so that all tables owner will be "postgres" user.
3.
On 18/02/10 10:34, dipti shah wrote:
Actually, I don't want table owners to drop the table using DROP command
directly. They have to use stored procedure to drop the table.
Then don't let them own the table. Or rather, the role they log in to
the database as shouldn't.
--
Richard Huxton
Actually, I don't want table owners to drop the table using DROP command
directly. They have to use stored procedure to drop the table.
Thanks,
Dipti
On Thu, Feb 18, 2010 at 4:01 PM, Richard Huxton wrote:
> On 18/02/10 10:23, dipti shah wrote:
>
>> Thanks Richard. That makes sense. If I want to
On 18/02/10 10:23, dipti shah wrote:
Thanks Richard. That makes sense. If I want to restrict DROP for any table
then do I need to REVOKE permissions individually on tables.
Revoke DROP ON MyTable from PUBLIC;
I want to avoid doing it so I am wondering if I can define/grant the
permission a
Op 18-02-10 11:07, Richard Huxton schreef:
On 18/02/10 10:02, Antonio Goméz Soto wrote:
if I define a pg/pgsql function, and I call that outside a transaction,
does it create one for itself? Or should I add BEGIN and COMMIT
statements within
the function?
You can't call a function outside a t
Thanks Richard. That makes sense. If I want to restrict DROP for any table
then do I need to REVOKE permissions individually on tables.
Revoke DROP ON MyTable from PUBLIC;
I want to avoid doing it so I am wondering if I can define/grant the
permission at database level so that nousers can dir
On 18/02/10 10:02, Antonio Goméz Soto wrote:
if I define a pg/pgsql function, and I call that outside a transaction,
does it create one for itself? Or should I add BEGIN and COMMIT
statements within
the function?
You can't call a function outside a transaction. Every statement in
PostgreSQL i
all statements in postgresql are self contained transactions, and you cannot
change that.
To answer your question directly, you don't have to, it will all be a
transaction.
The best example of that is to run following query in psql:
CREATE TEMP TABLE foo() ON COMMIT DROP;
the table will not exi
On 18/02/10 08:53, dipti shah wrote:
Hi,
Is it possible to define the permissions at database level such that no
users(except postgres) can execute DROP, ALTER, TRUNCATE commands directily?
Users have to use the given stored procedures.
1. Place users into appropriate groups (makes it easier t
Hello,
if I define a pg/pgsql function, and I call that outside a transaction,
does it create one for itself? Or should I add BEGIN and COMMIT statements
within
the function?
Thanks,
Antonio.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscrip
Thanks a lot Jayadevan.
I was unaware of temp table kind of functionality exists in postgres.
Now i updated functions as follows-
I have one question - if is there any better way of checking if temporary
table already created for the given session package(other than one i used
to capture as exc
venkatra...@tcs.com writes:
> I am new to Postgre. We are migrating an oracle db to postgre. In
> oracle we have used so many packages. As per my understanding, there
> is no oracle package like functionality in postgre. I was just trying
> to find some way to migrate ocale packages to postgre.
T
Carsten Kropf wrote:
Hi *,
I have the following problem:
I wanted to add a new type that supports modifiers (comparable to
numeric/varchar). I succeeded in adding the type modifier functions to my new
type. These methods are called and the modifier is set. However the modifiers
are not applied
AI Rumman wrote:
* What is the difference between shared_buffers and effective_cache_size?
This whole topic is covered at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server and the
additional references that document leads to.
* If I set effective cache size 1GB for db1 and 500 MB
Hi,
Is it possible to define the permissions at database level such that no
users(except postgres) can execute DROP, ALTER, TRUNCATE commands directily?
Users have to use the given stored procedures.
Thanks,
Dipti
In response to AI Rumman :
> * What is the difference between shared_buffers and effective_cache_size?
effective_cache_size:
Sets the planner's assumption about the effective size of the disk cache
that is available to a single query. This parameter has no effect on the
size of shared memory alloc
Hi Posgre Developers,
Common table for all packages
table Package_Variable_Table :-
For Storing Package public and private Variables This table will be
common for all packages.
to distinguish between different sessions, it uses unique session id. Get
and Set functions used to access these
* What is the difference between shared_buffers and effective_cache_size?
* If I set effective cache size 1GB for db1 and 500 MB for db2, then what
will happen to the system memory usage?
Anyone please tell me.
53 matches
Mail list logo
