You may get problems. At least we did.
Having a long term transaction which seemingly just was one Begin with
nothing, we encountered a siginifficant decrease of performance after some
days (70 tx/sec)
During that the pg_subtrans dir filled up with files and the IO-reads of the
disk as well. After
Maybe you're looking for the VOLATILE attribute of a function.
For the query optimizer it depends on what type of function you have how
often it is called.
|-Original Message-
|From: Din Adrian [mailto:[EMAIL PROTECTED]
|Sent: Dienstag, 05. Juli 2005 01:10
|To: Zac; pgsql-sql@postgresql.o
What's your experience concerning unit tests for stored procedures on
postgres?
I'd expect to write wrappers for any *unit - programming language and use
its *unit variant to do the actual testing.
>From my feeling SQLunit is kind of too xml-ish and I'm not sure wheter I can
save the output of som
|> > I disagree. In several relations (views of the world) one
|needs to have a
|> > hand full of well defined values while
|> > integers or bools are not appropriate and strings are too
|free form.
|> > For example male female or true and false. Whilst the
|second has a well
|> > known type, ot
Hi,
I've a prolem inserting records in a view using different ways.
I want to insert either way, with PK given and without PK which should then
be taken by its DEFAULT stanza:
insert into a_and_b(a) values (537)# id not given, self assigned
insert into a_and_b(x) values (true)# id not give
|
|I personally think that the ENUM data type is for databases
|that are not well
|designed. So, if you see the need for ENUM, that means you
|need to re-think
|your data design.
|
I disagree. In several relations (views of the world) one needs to have a
hand full of well defined values while
Hi,
I'm currently trying to make a table (where many fcns depend on) become a
view.
Thus I did a _truncate_ and lots of _alter table drop constraint_ and _drop
index_
and then
CREATE OR REPLACE RULE "_RETURN" AS
ON SELECT TO "smsMessagesrewtet"
DO INSTEAD
SELECT "MessageID",.
Post
Keep in mind, though. Using a DOMAIN in some definition 'seals' the domain.
Yo can't change the domain unless you drop all dependent objects
|-Original Message-
|From: Veikko Mäkinen [mailto:[EMAIL PROTECTED]
|Sent: Mittwoch, 22. Juni 2005 15:14
|To: pgsql-sql@postgresql.org
|Subject: Re:
Hi,
I have currently trouble working with boolean values and variables in
functions.
As one would expect, a
select '1'::bool, 't'::bool, 'true'::unknown::boolean
works.
As a select '1' tells us this seems as a conversion unknown->bool
or ??maybe?? a boolean literal??
what-o-ever, at least my
Hi,
in the course of my investigation on how to agglomerate or concat several
tables using a view or functions the following little HOW-TO felt out. It is
kind of a full fledged example of how to coalesce two tables using a view.
However it is as it is and I am more less new to writing rules and t
|
|I am a new postgres user
|
|I want to get a list of tables from pg_tables where tables are like
|‘%wo%’ (for example).. and then query that list ….
|
|Select count(*) from tableVARIABLENAMEFROMFIRSTQUERY
|
|In SQL SERVER I can do that using cursor but in postgresql I don’t
|understand how to
Hi,
I'm looking for people who have got experience at splitting a table with
heavy records into two relations.
In my case there exists one table with the mentioned heavy records. These
are processed by a statemachine. Thus a bunch of columns gets changed
several times in the livetime of one recor
;
|-Original Message-
|From: Markus Bertheau [mailto:[EMAIL PROTECTED]
|Sent: Dienstag, 07. Juni 2005 01:17
|To: KÖPFERL Robert
|Cc: pgsql-sql@postgresql.org
|Subject: RE: [SQL] SQL equivalent to nested loop
|
|
|Dnia 06-06-2005, pon o godzinie 20:08 +0200, KÖPFERL Robert napisał(a):
|&
This is basicly a Join, a cross table
i.e.
select b.w from table a, table b where ...
|-Original Message-
|From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
|Sent: Montag, 06. Juni 2005 18:53
|To: pgsql-sql@postgresql.org
|Subject: [SQL] SQL equivalent to nested loop
|
|
|Hi,
|
|I bas
|-Original Message-
|From: Marc Wrubleski [mailto:[EMAIL PROTECTED]
|Sent: Mittwoch, 01. Juni 2005 16:15
|To: pgsql-sql@postgresql.org
|Subject: [SQL] Returning a Cross Tab record set from a function
|
[...]
|
|It seems I can do this from any higher level language, but it drives me
|crazy
You could have a look at the OFFSET and LIMIT modifiers
as for untested example
select ((select max( "AValue") from table group by "Num") - "AValue") as
difference from table order by "AValue" desc offset 1
this says: give me a inversed ordered AValue-list but ommitting the first
(biggest) and su
|
|You can't, at the moment, except by parsing the text message.
|
|The "error fields" facility in the FE/BE protocol could be extended
|in that direction, and I think there's already been some discussion
|about it; but no one has stepped up with a concrete proposal, much
|less volunteered to do t
Hi,
I've got a table whose records are more less big. There's however jus one
Int-column changed frequently.
According to postgres' MVCC a whole record gets written, even if just one
bit was changed.
I think of splitting the table now in two parts, connected via the former
PK.
so like:
a|b|data |
Hi,
I'm currently on retrieving meta infromation about db-schemas.
As I found out, pg_proc relation provides me with data about defined stored
procedures. Togehter with other relations as pg_type I can retrieve readable
information, like:
select proname, pd.description FROM pg_proc pp left outer
Your're looking for the interval data type.
timestamp - timestamp
or date - date can be compared with interval
CURRENT_TIMESTAMP - '4d'::interval => today - 4 days
|-Original Message-
|From: [EMAIL PROTECTED]
|[mailto:[EMAIL PROTECTED]
|Sent: Montag, 23. Mai 2005 11:52
|To: pgsql-sql@p
Hi,
I had no try wheter this solves your problem, but have you conciddered using
an array or a self-written fcn which dicards your results
i.E. SELECT my_discard_but_last( q1(), q2(), q3());
or SELECT ARRAY[ q1(), q2(), q3() ];
or SELECT ROW(q1(), q2(), q3()) as my_tripel_type;
Have you consi
You should considder a variation as Volkan suggested.
Otherwise the EXECUTE command might be what you are looking for.
Execute a sql made up in a string
|-Original Message-
|From: bandeng [mailto:[EMAIL PROTECTED]
|Sent: Donnerstag, 19. Mai 2005 04:11
|To: pgsql-sql@postgresql.org
|Subjec
|PFERL_Robert?= <[EMAIL PROTECTED]> writes:
|> Thus I run VACUUM ANALYZE; every night by cron. However I
|keep getting
|> ERROR: tuple concurrently updated
|
|Have you got other processes also doing VACUUM ANALYZE? The only known
|reason for this to happen is that two processes concurrently
|A
Hi,
I have got some kind of FIFO-queue table.
New records are inserted and the oldest are DELETEd.
Thus I run VACUUM ANALYZE; every night by cron. However I keep getting
ERROR: tuple concurrently updated
My research lead me to the point that this is VACUUM tothether with the
INSERTs being issued
You are most probably missing entries in pg_hba.conf
and the listen= directive in postgres.conf
|-Original Message-
|From: Adriaan Botha [mailto:[EMAIL PROTECTED]
|Sent: Freitag, 22. April 2005 11:23
|To: 'PostgreSQL'
|Subject: [SQL] How do I connect with something like JDBCManager to
|Po
Hi all,
I think I have got a usual problem. I'm asking here, however, because I
wonder why it works this way.
The problem is to write a fcn that eihter returns all records or just
one/none filtered by some expression. For example get a value by id or
return all values if the given id is null.
F
You're
most probably missing a Where clause after the parentensis.
see:
-Original Message-From: Joel Fradkin
[mailto:[EMAIL PROTECTED]Sent: Dienstag, 19. April 2005
16:06To: pgsql-sql@postgresql.orgSubject: [SQL] trying
to do an update a bit confused.
update tblcase s
Postgres has the weird behavour to compare identifies
case sensitive BUT to downcast any non-quoted identifier inside an SQL
statement.
So it
is reccomended to just use lower case (for readability)
-Original Message-From: Muhammad Nadeem Ashraf
[mailto:[EMAIL PROTECTED]Sent: D
That was a nice answer - rather compleete.
However at least I am questioning myself for a long time about what happens
if one does a select from a SRF. The function may return millions of records
(i.e. select * from x where a>1). Is this data streamed through the query
process or does postgres cre
Have a try with RAISE NOTE or RAISE EXCEPTION
keep in mind that exceptions should be exceptional. So a good idea of
whether to use them is to ask 'Do I expect such error' or 'is an explicit
error useful for the caller'. I'ts often better to just return an empty
relation
|-Original Message-
Hi,
I've written a function but I don't understand the the plan, the planner
makes.
If variables are replaced, the function looks like that:
select a,b,c from "Tbl1" where (a='454') or ('454' is null);
a has got an btree-Index.
explain verbose tells me that Postgres wants to do a SEQSCAN
If th
Hi,
we have got some tables (uw?) and functions. One function is defined like
get_abc():
SELECT a,b,c from table_x;
What happens if I query something like
SELECT a,b from get_abc() where a=5;
while table_x is rather big?
Will PSQL at first query all records of table_x and then apply a where
Coming from functional programming, I often wish to write something like
that:
(LAMDA "expesiveFcn"(x y z) as exfcn
update "Tbl5" SET "Column" = exfcn
)
In this case "expensiveFcn" is VOLATILE...
Is there a way?
At least: substituting the lambda by a select doesn't work with update as
OK, the usual thing:
There exists a DB-schema. It is on one hand already in production usage. On
the other hand it is still being developed as functions and non-structural
stuff are concerned.
I found out that EMS Database Comparer helps to replicate the schema
differences in form of SQL-statement
In pgadmins SQL-window SQL is the 'language' of choice. Or it is rather the
only language. Thus if you intend to program plTk or PL/pgSQL, there's no
way around defining a function.
(At first you have to define a new language in your schema)
C:\> -Original Message-
C:\> From: Steve - DND
Otherwise you can treat this as a subselect and suround it with another
select.
Like
select * from () order by orderno;
C:\> -Original Message-
C:\> From: Bruno Wolff III [mailto:[EMAIL PROTECTED]
C:\> Sent: Mittwoch, 23. Februar 2005 18:20
C:\> To: WeiShang
C:\> Cc: pgsql-sql@postgresql
Hi all,
I have got two database schemas. They're rather independend. Thus they are
in two databases. However there is one function that needs access to the
other database.
As I found out, I have two choices:
*Using schemas and put the schemas tighter together (via interdependencies).
Dumping dist
You may possibly solve the problem with the inheritted tables with the
RULE-System of pgsql. But this seems oversized to me.
You could rather create several tables, each with its matching
rights/privileges and 'connect' them via an 1:1 relation.
The 'real' way such thing is normally done is to wri
At least for between, I read that pgSQL rewrites it to a (a -Original Message-
> From: Keith Worthington [mailto:[EMAIL PROTECTED]
> Sent: Mittwoch, 16. Februar 2005 17:36
> To: PostgreSQL SQL
> Cc: Sean Davis; Scott Marlowe
> Subject: Re: [SQL] More efficient OR
>
>
> > > Hi All,
> > >
Hi,
yes that's my problem.
I've got a table and I put lots of contraints on it so that data stays
consistent. One constraint calls a fcn to do some kind of count() over that
table but it omits the 'to be inserted record'. What to do?
concrete problem:
Suppose a table
i | tel | status | ...
..|
> So it should work to do "SELECT int4($1::bit varying)" and then pass
> 'xBEEF' as the string value for the parameter.
>
> regards, tom lane
Thanks, that worked for me
---(end of broadcast)---
TIP 3: if posting/reading thro
I intend to retrieve an int value in an integer variable from a string with
a hexadecimal notation of a number.
Which function is appropriate to do
i int4
i = ???('BEAF')
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [E
Are you using Fedora with SELinux or just
SELinux?
-Original Message-From: Premsun Choltanwanich
[mailto:[EMAIL PROTECTED]Sent: Montag, 07. Februar 2005
05:41To: pgsql-sql@postgresql.orgSubject: [SQL] How can
I use large object on PostgreSQL Linux Version?
For first inform
Hi,
Consider one postmaster that manages multiple databases (logical names)
Is it possible (and how) to access a stored procedure or view/Table which
resides in DB aaa if your DB-connection has currently the context on DB bbb?
So: I login on db bbb as a user who has rights on both DBs (aaa,bbb).
Hi,
I'm trying to find an equivalent plpgsql function as this:
func x returns SETOF "Tablename" AS
'
Select * from "Tablename";
' language sql
How is this accomplished with plpgsql while not using a loop or a second and
third temporal table?
---(end of broadcast)
I just tried hard to return
a single record fromout a plpgsql-function. While the (otherwise excelent)
documentation didn't give me an answer, I found out that this works:
select into ret false, balance, balance;
return ret;
while ret is a composite type.
This construction howeve
Hi all and Michael.
An MS-SQL experienced developer warned me that on MS-SQLsvr a whole table
gets locked if a certain percentage or amount of records are locked due to
an update. And then shortly nothing goes.
Does there exist a similar behaviour on pgSQL? Get tables locked if too many
records ar
In order to learn SQL-Stored Procedure techniqes I'm looking for a series of
examples.
Where can I find examples of SQL and PL/pgSQL based stored procedures?
Or any of you who wants to donate some?
---(end of broadcast)---
TIP 8: explain analyze is y
Hi,
I'm currently writing a function which encapsulates a delete and should
return a bool as indicator for success.
I tried:
DELETE FROM "TariffDetails" WHERE "TariffId"=$1 and "BNumberPrefix"=$2;
SELECT TRUE;
but this makes me not happy.
How can I distingruish wehter DELETE affected
Hi,
coming from imperative programming paradigma, I'm currently trying to
express something like that in _SQL_:
It should be atomic and like an API for a user. I'm therefore writing
functions:
CRETE FUNC...
c := SELECT x,y,z FROM table_a WHERE...
IF COUNT(c)=1 then
INSERT / DELETE ... W
Hi,
suppose I have a let's say heavy used table. There's a column containing
UNIQUE in4
values. The data type musn't exceed 32-Bit. Since however the table is heavy
used 2^32 will be reached soon and then? There are far less than 4G-records
saved thus these values may be reused. How can this be ac
>
> It really is. In fact, the feature was (IIRC) somewhat
> controversial, because there are all sorts of decisions that need to
> be made about what to do with incompatible types. What if you change
> from int8 to int4? What about varchar(4) to char(4)? Just to name
> two simple-minded exa
> -Original Message-
>
> That's exactly the error you'd get on a pre-8.0 system that doesn't
> support altering a column's type. Are you looking at 8.0
> documentation
> but running a 7.x server? What does "SELECT version();" show?
Yes, that's it. I am looking into an 8.0 doc while
Hi, I am perplexed.
I tried to change the type of a column using the syntax I found in the
[ALTER TABLE] section:
ALTER TABLE "Mailboxes" ALTER COLUMN "Status" TYPE int4;
This shuld be no problem since the current type acutally is int4 and the
names are copy'n'pasted. The server responds as follo
Hi,
since I am new to writing stored procedures I'd like to ask first bevore I
do a mistake.
I want to implement some kind of queue (fifo). There are n users/processes
that add new records to a table and there are m consumers that take out
these records and process them.
It's however possible for
Hi,
with what constraint or how can I ensure that one of my tables has exact one
record or 0..1 records?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
56 matches
Mail list logo
