Hi all
I have been reading up on 'optimistic concurrency
control'.
The following quote is taken from the on-line help
for MS SQL Server 2000 -
"Optimistic concurrency control works on the
assumption that resource conflicts between multiple users are unlikely (but not
impossible), and a
On Tue, 11 Jan 2005, Bruno Wolff III wrote:
> On Tue, Jan 11, 2005 at 15:40:07 +,
> Peter Childs <[EMAIL PROTECTED]> wrote:
> >
> > I thought pg_autovaccum was going to be built into 8.0 or was that
> > only a rumor.
>
> Due to some unfortunate things it was not ready by the beta cutof
On Thu, Jan 13, 2005 at 11:49:00AM +0200, Frank Millman wrote:
> I have been reading up on 'optimistic concurrency control'.
>
> The following quote is taken from the on-line help for MS SQL Server 2000 -
> MS SQL Server uses a 'timestamp' column to check if a row has been
> altered since it wa
Frank Millman wrote:
> MS SQL Server uses a 'timestamp' column to check if a row has been
> altered since it was read. The equivalent in PostgreSQL seems to be
> the system column 'xmin'. However, the notes say 'You do not really
> need to be concerned about these columns, just know they exist.' Is
UP !
Aren't there any way to debug server programming functions (whatever the language) ??
Benjamin.
[EMAIL PROTECTED]
Envoyé par : [EMAIL PROTECTED]
12/01/2005 17:15
Pour : pgsql-general@postgresql.org
cc :
Objet : [GENERAL] Debuggi
Hi people,
this is my first mail to the group. i am having a problem with the select query.
There are a few values in few columns of a table with some special characters like ~E and ~A and what all, and when i query the database with the select query then the ~E is getting converted into a new
Merlin Moncure wrote:
6. for large tables, you can get a pretty accurate count by doing:
select count(*) * 10 from t where random() > .9;
on my setup, this shaved about 15% off of the counting time...YMMV.
That's an interesting idea, using sampling to get an estimate.
Thanks for the tip.
--
dave
-
Hi,
I need some information regarding pgsqlODBC driver on Windows2000. We use an
application which inserts records pallely into an SQL database via ODBC.
It estableshes 16 pallel connections and continously inserts data. The
software works well on a test environment with MS Access and MS ODBC driv
I need to create a new database v7.4.6 on a new server that will act as
a copy of our current live 7.4.1 database. Ultimately replicated using
slony (we hope)...
I have installed 7.4.6 on the new server - no problems so far.
Now I'm trying to figure out what locale options were passed to initdb
On Wed, Jan 12, 2005 at 11:30:50PM -0700, Michael Fuhr wrote:
> On Wed, Jan 12, 2005 at 11:49:12AM -0700, Ed L. wrote:
> >
> > Is it possible via SQL query to tell how long a transaction has been open?
>
> I'm not aware of a way to find out when a transaction started, but
> if you have stats_comm
I am trying to add a unique constraint on a column that can be null. The
documentation states that null is treated as non equal values but I want
them to be equal. Is there another way of doing this other than writing
a before insert trigger?
--
Dave Smith
CANdata Systems Ltd
416-493-9020
-
David Garamond <[EMAIL PROTECTED]> writes:
> Merlin Moncure wrote:
> > 6. for large tables, you can get a pretty accurate count by doing:
> > select count(*) * 10 from t where random() > .9;
> > on my setup, this shaved about 15% off of the counting time...YMMV.
>
> That's an interesting idea, u
Hi,
I have a table with many triggers. Now, it can happen that in middle of
execution one of table's triggers there will be unknown error (like
"NEW" is not assigned yet) - is there a way to trace which trigger fired
this error ?
Thanks in advance.
ML
---(end of broadca
We are pleased to announce the release of the DBManager Professional 3.0.2.
This version is a major bug fix with a small number of new features:
Fixes
a.. Fixed Load Dump which affected most of the engines supported
b.. Closing Workspace with other Views opened will close all views
c.. Fixed
[snip]
> The database could be clever and implement the same kind of sampling vacuum
> does. That picks a random sampling of pages from the table without using an
> index. But there's no way to implement the same kind of behaviour from the
> user-visible features.
... meaning perhaps a new keyword
[EMAIL PROTECTED] wrote:
Aren't there any way to debug server programming functions (whatever the
language) ??
What's wrong with gdb?
Joe
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining
Dave Smith <[EMAIL PROTECTED]> writes:
> I am trying to add a unique constraint on a column that can be null. The
> documentation states that null is treated as non equal values but I want
> them to be equal. Is there another way of doing this other than writing
> a before insert trigger?
UNIQUE
I do use gdb with eclipse but I don't manage to enter the source code of my spi C functions.
Suppose I have a SPI functions that :
1) connects to the db (SPIConnect)
2) Executes a simple select (SPIExec)
3) Writes result of the select in a text file
4) disconnects from the db (SPIFinish)
Then I
Csaba Nagy <[EMAIL PROTECTED]> writes:
> [snip]
> > The database could be clever and implement the same kind of sampling vacuum
> > does. That picks a random sampling of pages from the table without using an
> > index. But there's no way to implement the same kind of behaviour from the
> > user-vi
Marek Lewczuk <[EMAIL PROTECTED]> writes:
> I have a table with many triggers. Now, it can happen that in middle of
> execution one of table's triggers there will be unknown error (like
> "NEW" is not assigned yet) - is there a way to trace which trigger fired
> this error ?
Use PG 7.4 or later
On Thu, Jan 13, 2005 at 08:45:38AM -0700, Ed L. wrote:
> On Thursday January 13 2005 5:50, Alvaro Herrera wrote:
> > On Wed, Jan 12, 2005 at 11:30:50PM -0700, Michael Fuhr wrote:
> > > On Wed, Jan 12, 2005 at 11:49:12AM -0700, Ed L. wrote:
> > > > Is it possible via SQL query to tell how long a tra
On Wednesday January 12 2005 11:30, Michael Fuhr wrote:
> On Wed, Jan 12, 2005 at 11:49:12AM -0700, Ed L. wrote:
> > Is it possible via SQL query to tell how long a transaction has been
> > open?
>
> I'm not aware of a way to find out when a transaction started, but
> if you have stats_command_stri
On Thursday January 13 2005 5:50, Alvaro Herrera wrote:
> On Wed, Jan 12, 2005 at 11:30:50PM -0700, Michael Fuhr wrote:
> > On Wed, Jan 12, 2005 at 11:49:12AM -0700, Ed L. wrote:
> > > Is it possible via SQL query to tell how long a transaction has been
> > > open?
> >
> > I'm not aware of a way to
I don't know if there is some built in way of doing it, but we have
implemented the following in all our trigger code:
DECLARE
dbg BOOLEAN DEFAULT False; -- debug messages flag
BEGIN
IF dbg THEN
RAISE NOTICE ''% (%)'', TG_NAME, TG_OP;
END IF;
Wh
[snip]
> See:
>
> http://www.jlcomp.demon.co.uk/faq/random.html
>
> I think the Oracle syntax looks like
>
> SELECT * FROM foo SAMPLE (0.1)
>
> I don't think I would have picked this syntax but it seems like a better idea
> to copy the existing practice rather than invent a new one.
>
> Th
> MS SQL Server uses a 'timestamp' column to check if a row has
> been altered since it was read. The equivalent in PostgreSQL
> seems to be the system column 'xmin'. However, the notes say
> 'You do not really need to be concerned about these columns,
> just know they exist.' Is it ok to use xmin
[EMAIL PROTECTED] writes:
> Then I have a C (libpq) client program that calls my spi functions like=20
> this :
> PQExec(conn,"SELECT mySpiFunction()");=20
> the problem is -> I can't step into this line !
Of course not --- the execution of mySpiFunction is in a different
process. You need to s
On Thu, Jan 13, 2005 at 04:23:29PM +0100, [EMAIL PROTECTED] wrote:
> Then I have a C (libpq) client program that calls my spi functions like
> this :
>
> PQExec(conn,"SELECT mySpiFunction()");
>
> the problem is -> I can't step into this line !
Of course not. The processing is done server
Greetings,
Is it possible to allow TCP/IP connections from additional hosts (IPs)
without restarting postgresql?
The issue i'm having is that i've got a postgresql-7.4.6 box that has
a DB running on it for a j2ee based application on a separate server.
Now i need to add an additional DB for a dif
On Thursday January 13 2005 10:09, Michael Fuhr wrote:
>
> For idle transactions pg_stat_activity shows " in transaction"
> and the query_start column shows when the transaction became idle
> (i.e., when the last statement completed). So if long-lived idle
> transactions are the problem, then at l
I've run into these quirks before using ODBC.
If a table is huge, the ODBC driver will croak if you try to grab the
entire table all at once. I end up needing to use LIMIT OFFSET
to get chunks of tables and then piece the table together on the client.
For a transaction encompassing a l
Lonni J Friedman wrote:
> Now i need to add an additional DB for a different server, but I
> can't afford any down time to restart postgresql for the changes in
> pg_hba.conf to take effect.
pg_ctl reload or (equivalently) kill -HUP
--
Peter Eisentraut
http://developer.postgresql.org/~petere/
-
On Thu, Jan 13, 2005 at 08:44:56AM -0700, Ed L. wrote:
> On Wednesday January 12 2005 11:30, Michael Fuhr wrote:
> >
> > I'm not aware of a way to find out when a transaction started, but
> > if you have stats_command_string enabled then you can query
> > pg_stat_activity to see when a session's cu
On Thu, Jan 13, 2005 at 09:50:38AM -0300, Alvaro Herrera wrote:
> On Wed, Jan 12, 2005 at 11:30:50PM -0700, Michael Fuhr wrote:
> >
> > I'm not aware of a way to find out when a transaction started, but
> > if you have stats_command_string enabled then you can query
> > pg_stat_activity to see when
wouah. Ok. Looks like programming deficient skills ... :)
Thank you.
By the way,
I have been using DB2 and its Call Level Interface (CLI) API and it was great because you have the same API (based onb ODBC) for writing server side stored procedures and external functions.
I guess that's why I was
Dave Smith <[EMAIL PROTECTED]> writes:
> On Thu, 2005-01-13 at 10:12, Doug McNaught wrote:
>> Are you really saying that you want 'NULL = NULL' to return 't'?
> Yes
Well, that's not how NULL works.
-Doug
---(end of broadcast)---
TIP 2: you can g
On Thu, Jan 13, 2005 at 08:44:56AM -0700, Ed L. wrote:
> Yes, I see that in 7.4 (not in 7.3). But my purpose would be to remotely
> identify long-open transactions that are causing table bloat by making
> vacuum fail to reclaim space, so it seems I need the transaction start
> time, not query s
On Thu, Jan 13, 2005 at 08:55:08AM -0800, Lonni J Friedman wrote:
> Is it possible to allow TCP/IP connections from additional hosts (IPs)
> without restarting postgresql?
"pg_ctl reload" should cause the server to re-read its configuration
files without shutting down and restarting. See the sec
On Thu, Jan 13, 2005 at 12:04:28PM -0700, Ed L. wrote:
> It'd be nice if pg_stat_activity.transaction_start were added in the future
> for a 100% answer, but I'm not sure there's much interest in this apart
> from our needs.
I wouldn't expect that to be hard to add. Consider submitting a
patch
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
You could always create and configure a new data directory, then start
a new instance of the postmaster running on a different port. Use the
new instance for the other client. That would separate the user
accounts, too...
On Jan 13, 2005, at 11:55
On Thu, Jan 13, 2005 at 10:33:50AM -0700, Ed L. wrote:
> On Thursday January 13 2005 10:09, Michael Fuhr wrote:
> >
> > For idle transactions pg_stat_activity shows " in transaction"
> > and the query_start column shows when the transaction became idle
>
> That will help, thanks. Unfortunately, th
Hi
I come from a MS-SQL background and am trying to
figure out what is wrong with the function below:
*
CREATE OR REPLACE FUNCTION GetAccountInfo (p_AccID
int)RETURNS record AS$$DECLARE r_Return
record;
On Thursday January 13 2005 11:37, Michael Fuhr wrote:
>
> That'll show which transaction is oldest but not how long it's been
> open or idle, i.e., whether it's "long-open" or not. I assumed,
> perhaps incorrectly, that he was already looking at pg_locks and
> wanted to find out which of those tr
Thanks to all who responded. That works perfectly.
On Thu, 13 Jan 2005 19:28:02 +0100, Peter Eisentraut <[EMAIL PROTECTED]> wrote:
> Lonni J Friedman wrote:
> > Now i need to add an additional DB for a different server, but I
> > can't afford any down time to restart postgresql for the changes i
I’m new both to databases and postgres, so forgive me
if this is a stupid question.
Where do people usually enforce business rules? In the
client application or in the database?
For example, I might have a rule “don’t allow
customers to enter an order if their account
is delinquen
On Thu, 2005-01-13 at 11:02, Doug McNaught wrote:
> Dave Smith <[EMAIL PROTECTED]> writes:
>
> > On Thu, 2005-01-13 at 10:12, Doug McNaught wrote:
>
> >> Are you really saying that you want 'NULL = NULL' to return 't'?
>
> > Yes
>
> Well, that's not how NULL works.
In this instance, just creat
Hi
I come from a MS-SQL background and am trying to figure out what is wrong
with the function below:
*
CREATE OR REPLACE FUNCTION GetAccountInfo (p_AccID int)
RETURNS record
AS
$$
DECLARE
r_Return record;
BE
I make the changes into the pg_hba.conf and do a kill -HUP 'PID' for
postgres to reread the file. The pid number can be found in the
~postgres/data/postmaster.pid file
Woody
IGLASS Networks
www.iglass.net
-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
On Thu, Jan 13, 2005 at 07:11:09PM +0100, Martijn van Oosterhout wrote:
>
> Wouldn't the transaction ID be more useful. An earlier transaction ID
> obviously started earlier. So you should be able to identify the oldest
> transaction. Would the transaction ID field in pg_locks do?
That'll show wh
On Thu, Jan 13, 2005 at 08:55:08AM -0800, Lonni J Friedman wrote:
> The issue i'm having is that i've got a postgresql-7.4.6 box that has
> a DB running on it for a j2ee based application on a separate server.
> Now i need to add an additional DB for a different server, but I can't
> afford any d
Lonni J Friedman <[EMAIL PROTECTED]> writes:
> Is it possible to allow TCP/IP connections from additional hosts (IPs)
> without restarting postgresql?
> The issue i'm having is that i've got a postgresql-7.4.6 box that has
> a DB running on it for a j2ee based application on a separate server.
>
On Thu, Jan 13, 2005 at 07:58:33PM +0200, Craig Bryden wrote:
> When I run select * from GetAccountInfo (100)I get the following
> error message:
> ERROR: a column definition list is required for functions returning "record"
If the function will always return the same row type then creat
Hello
try
select * from GetAccountInfo (100) as (integer, integer, integer);
I don't know types of fields a.Field1, a.Field2, a.Field4. I expect for
example all are integer.
regards
Pavel Stehule
On Thu, 13 Jan 2005, Craig Bryden wrote:
> Hi
>
> I come from a MS-SQL background and am trying
On Thu, 13 Jan 2005, Craig Bryden wrote:
> When I run select * from GetAccountInfo (100)
> I get the following error message: ERROR: a column definition list is
> required for functions returning "record"
You need to say something like:
select * from GetAccountInfo(100) AS foo(field1 int, fiel
John Cunningham wrote:
> concerned that if I drop the number of connections to less than the
> number of databases I have, that pgpool would open the limit of
> connections, hold them open and not allow any connections to the
> remaining databases. Is this a concern? If I set up pgpool will I
> h
I would suggest doing both, really: have the client check, so that options which are not available to the user appear "disabled" in the first place, and have the server check as well, so that "fake" clients (clients other than yours) attempting a transaction cannot perform invalid operations in ord
Hmmm - so to bring one more idea here...
The usage of each of these systems will be different on different days
- Monday may have high usage on one or two databases and Wednesday may
be high on 20 others. This makes me thing that configuring thigs this
way would mean that pgpool would be limiting
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
Or look at:
http://archives.postgresql.org/pgsql-sql/2003-08/msg00286.php
(and possibly its follow-up)
On Jan 13, 2005, at 3:16 PM, Scott Marlowe wrote:
On Thu, 2005-01-13 at 11:02, Doug McNaught wrote:
Dave Smith <[EMAIL PROTECTED]> writes:
On Thu, 200
On Thu, Jan 13, 2005 at 09:01:08 -0500,
Dave Smith <[EMAIL PROTECTED]> wrote:
> I am trying to add a unique constraint on a column that can be null. The
> documentation states that null is treated as non equal values but I want
> them to be equal. Is there another way of doing this other than wri
"Mike G." <[EMAIL PROTECTED]> writes:
> I have a function that reads through one table via a cursor to make updates
> to a different table. There is a row in this table with a column that has a
> null value. The following row, in that same column, is not null.
> When the cursor loops through
I was considering putting pgpool in to place and was hoping to hear
some feedback from those who use it. I am mostly concerned about the
configuration I have.
In my setup, any one database server contains between 100 and 300
databases on it, each of which may be accessed at any time by one of
sev
Yes
On Thu, 2005-01-13 at 10:12, Doug McNaught wrote:
> Dave Smith <[EMAIL PROTECTED]> writes:
>
> > I am trying to add a unique constraint on a column that can be null. The
> > documentation states that null is treated as non equal values but I want
> > them to be equal. Is there another way of d
thank you.
I use the cursor because I really do an update against a different table based
on a value from the select in the original table. I am eagerly awaiting 8.0
and the ability to issue an Update Table1 Set Table1.col = Table2.col Using
(Select y from Table2)
Mike
On Thu, Jan 13, 2005
Title: Version Control Software for Database Objects
We maintain multiple versions of our application's database and we are looking for version control software to help us automate this. Specifically, we would like to have a program that automatically tracks all changes to the database (tabl
On 1/13/05 9:50 AM, "Greg Stark" <[EMAIL PROTECTED]> wrote:
> I think the Oracle syntax looks like
>
> SELECT * FROM foo SAMPLE (0.1)
>
> I don't think I would have picked this syntax but it seems like a better idea
> to copy the existing practice rather than invent a new one.
Of course, in O
Wes <[EMAIL PROTECTED]> writes:
> On 1/13/05 9:50 AM, "Greg Stark" <[EMAIL PROTECTED]> wrote:
>
> Of course, in Oracle 'count(*)' is instantaneous. It doesn't have to count
> the physical records one by one.
That's simply false. Oracle does indeed have to count the records one by one.
It does
31 minutes in 2.6.10-1.8:
select kstime(), update_ranking_usuarios(), kstime();
kstime| update_ranking_usuarios | kstime
-+-+-
2005-01-13 20:27:56 | | 2005-01-13 20:58:46
(1 row)
5 minute
Jeff Davis <[EMAIL PROTECTED]> writes:
> I attached a proposed documentation patch. I wasn't able to readily see
> the implications of writing a function in SQL regarding an index, so
> perhaps this will help someone in the future (not that many people will
> attempt writing index access methods in
On Thu, 2005-01-13 at 15:18 -0800, Mark Dexter wrote:
> We maintain multiple versions of our application's database and we are
> looking for version control software to help us automate this.
> Specifically, we would like to have a program that automatically
> tracks all changes to the database (ta
Mark Dexter wrote:
We maintain multiple versions of our application's database and we are
looking for version control software to help us automate this.
Specifically, we would like to have a program that automatically tracks
all changes to the database (tables, views, functions, etc.) and assis
Tim Allen <[EMAIL PROTECTED]> writes:
> Mark Dexter wrote:
> > We maintain multiple versions of our application's database and we are
> > looking for version control software to help us automate this.
> > Specifically,
> > we would like to have a program that automatically tracks all changes to
"Rick Schumeyer" <[EMAIL PROTECTED]> writes:
> I'm new both to databases and postgres, so forgive me if this is a stupid
> question.
>
> Where do people usually enforce business rules? In the client application
> or in the database?
This isn't a stupid question. This is the type of question tha
Hi ...
I am using postgresql 7.4 on a pontencial large DB system, and I am
quite happy of the performance of this database, as for now. Only one
thing worrys me, and I like to get some pease to my mind about this.
I use normal tabel ID (SERIAL and BIGSERIAL) all over the place for FK
constaints
73 matches
Mail list logo
