* 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.
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
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
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
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
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
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.
This
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
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
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
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
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
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
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
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
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 d...@archonet.com wrote:
On 18/02/10 10:23, dipti shah wrote:
Thanks Richard. That makes
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
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: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
Thanks. I will do testing.
On Thu, Feb 18, 2010 at 4:29 PM, Richard Huxton d...@archonet.com 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
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:
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:
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.
Ivan Sergio Borgonovo m...@webthatworks.it 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
Yeb Havinga yebhavi...@gmail.com 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,
On Thu, Feb 18, 2010 at 10:33 AM, Little, Douglas douglas.lit...@orbitz.com
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
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 yebhavi...@gmail.com
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
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,
On Thu, Feb 18, 2010 at 12:37 PM, Richard Huxton d...@archonet.com 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
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
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
Hello
2010/2/18 wilcza...@op.pl:
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
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 mailing
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
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;
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
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();
On Thu, 2010-02-18 at 09:33 -0600, Little, Douglas wrote:
psql p1gp1 QUIT $LOGFile 21
\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
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
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 to
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:
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
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:
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 above
On Thu, Feb 18, 2010 at 8:46 PM, Jayadevan M
jayadevan.maym...@ibsplc.com 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
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
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 *
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
Lew no...@lwsc.ehost-services.com 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
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 that
Naoko Reeves naokoree...@gmail.com 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)
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;
53 matches
Mail list logo
