-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Jasen Betts
Sent: May 4, 2013 7:44 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Simple SQL INSERT to avoid duplication failed: why?
On 2013-05-01, Carlo
...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Steven Schlansker
Sent: April 30, 2013 7:10 PM
To: Carlo Stonebanks
Cc: 'Tom Lane'; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Simple SQL INSERT to avoid duplication failed: why?
On Apr 30, 2013, at 4:00 PM, Carlo
To: Carlo Stonebanks
Cc: Steven Schlansker; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Simple SQL INSERT to avoid duplication failed: why?
On Wed, May 1, 2013 at 7:16 AM, Carlo Stonebanks
stonec.regis...@sympatico.ca wrote:
Very good to know, Steve. We're on 9.0 right now but I
]
Sent: April 25, 2013 5:06 PM
To: Carlo Stonebanks
Cc: pgsql-general@postgresql.org
Subject: Re: Simple SQL INSERT to avoid duplication failed: why?
Carlo Stonebanks stonec.regis...@sympatico.ca writes:
Ok, I tried to be clever and I wrote code to avoid inserting duplicate
data.
The calling
Ok, I tried to be clever and I wrote code to avoid inserting duplicate data.
The calling function has a try-catch to recover from this, but I am curious
as to why it failed:
INSERT INTO
mdx_lib.acache_mdx_logic_address_validation
(
address,
postal_code,
address_id
Of Adrian Klaver
Sent: February 11, 2013 6:09 PM
To: Carlo Stonebanks
Cc: 'Postgres General'
Subject: Re: [GENERAL] Tcl PG on Win 7 64 bit - is it working for anyone?
On 02/11/2013 08:18 AM, Carlo Stonebanks wrote:
I had seen that thread, and that's how I came in contact with L J
Bayuk
]
Sent: February 10, 2013 10:13 AM
To: Carlo Stonebanks
Cc: 'Postgres General'
Subject: Re: [GENERAL] Tcl PG on Win 7 64 bit - is it working for anyone?
On 02/09/2013 09:39 PM, Carlo Stonebanks wrote:
I am actually in the same folder as the libpgtcl.dll, and that
particular failure would raise
I have been trying to get the Tcl package for client applications PgTcl to
work for some time now. I have tried the SourceForge pgtclng 2.0 project, I
have recompiled it with MinGW-64, I have installed PG 9.1.8 64 bit and made
sure PATH points to its lib folder so it can find libpq.dll. NO matter
...@gmail.com]
Sent: February 9, 2013 1:01 PM
To: Carlo Stonebanks
Cc: 'Postgres General'
Subject: Re: [GENERAL] Tcl PG on Win 7 64 bit - is it working for anyone?
On 02/09/2013 09:52 AM, Carlo Stonebanks wrote:
I have been trying to get the Tcl package for client applications
PgTcl to work
I would imagine he means, ‘Can PgAdmin be distributed in such a way that the
user does not have to setup DB connections themselves. But are preconfigured’.
If so, then this is a PgAdmin question, not a PostgreSQL question.
From: pgsql-general-ow...@postgresql.org
:06 AM
To: Merlin Moncure
Cc: Carlo Stonebanks; kesco...@estudiantes.uci.cu; pgsql-general@postgresql.org
Subject: Re: [GENERAL] What language is faster, C or PL/PgSQL?
On Mon, Feb 4, 2013 at 08:33:02AM -0600, Merlin Moncure wrote:
On Sat, Feb 2, 2013 at 11:36 AM, Carlo Stonebanks
stonec.regis
Here is an advantage Plpgsql has:
http://www.postgresql.org/docs/9.1/static/plpgsql-expressions.html
I guess you can offset this by creating your own prepared statements in C.
Otherwise, I cant think of how C could be slower. I would choose C for
functions that dont have SQL statements in
# delete from pref_users
where id not in (select distinct id from pref_money) limit 10;
ERROR: syntax error at or near limit
LINE 2: ...ere id not in (select distinct id from pref_money) limit 10;
Or this?
DELETE FROM pref_users
WHERE id IN (
SELECT id
FROM pref_users
As our production system are being upgraded to Windows 7 64-bit we are
finding that our Tcl apps which use the PgTcl client libraries are now
failing (couldn't load library libpgtcl.dll: invalid argument). We have
tried downloading the latest binaries (which are 32 bit) but the problems
persist.
...@postgresql.org] On Behalf Of David Fetter
Sent: January 31, 2013 1:50 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Pg Tcl - is it dying out?
On Wed, Jan 30, 2013 at 05:45:06PM -0500, Carlo Stonebanks wrote:
As our production system are being upgraded to Windows 7 64-bit we
-general-ow...@postgresql.org] On Behalf Of Adrian Klaver
Sent: January 31, 2013 6:01 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Pg Tcl - is it dying out?
On 01/31/2013 10:59 AM, Carlo Stonebanks wrote:
Was there something constructive you might have been asking about
that
you forgot
Nicely done, Merlin! Hope others with the same problem can find this post.
Thanks a lot.
-Original Message-
From: Merlin Moncure [mailto:mmonc...@gmail.com]
Sent: May 26, 2011 9:53 AM
To: Carlo Stonebanks
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Miidpoint between two long
I need to calculate the long/lat values between a line demarcated by two
long/lat points.
The points will be very close, but there is the 180 degree problem to
consider, so a simple average won't work.
Does anyone know of a function or have a formula that will work using geo
long/lat
A few years ago I asked about creating a single UPDATE statement to assign
id's from a sequence, with the sequences applied in a particular order. In
other words, order the table, then apply nextval-generated id's to the id
field in question.
Here is the original post:
to be filled and unique so that it can be
related to the data warehouse asset management and operation auditing
systems.
Thanks,
Carlo
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: April 12, 2011 8:15 PM
To: Carlo Stonebanks
Cc: pgsql-general@postgresql.org
Subject: Re
Whenever I attempt to create a sequence whether this is done directly via
CREATE SEQUENCE or indrirectly vial declaring a column as SERIAL, PG
truncates it to 64 characters. Is this a documented limitation? I can't find
a reference to it. Is it possible to change this limitation?
Our table
Whenever I attempt to create a sequence whether this is done directly via
CREATE SEQUENCE or indrirectly vial declaring a column as SERIAL, PG
truncates it to 64 characters. Is this a documented limitation? I can't find
a reference to it. Is it possible to change this limitation?
Our table
Message-
From: Adrian Klaver [mailto:adrian.kla...@gmail.com]
Sent: March 31, 2011 5:29 PM
To: Jerry Sievers
Cc: Carlo Stonebanks; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sequence names have 64 character limit?
On 03/31/2011 02:13 PM, Jerry Sievers wrote:
Carlo Stonebanksca
:15 PM
To: Carlo Stonebanks
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sequence names have 64 character limit?
On 03/31/2011 02:47 PM, Carlo Stonebanks wrote:
Thanks Adrian and Jerry.
Technically, the best way to know which sequence a column is dependent on
is
to actually query
We have procs that would benefit from returning IMMUTABLE results. The procs
are dependent on external tables that rarely change, but when they DO
change, it would be great if we could expire the cache that the procs read
from so that the procs are forced to re-evaluate the results.
Is this
Can anyone ta;lk to me about the PL/TCL implementation of the Unkown
function?
The docs are nice, but more of a tease.
http://www.postgresql.org/docs/8.3/static/pltcl-unknown.html
I have to make this work on Windows and Linux. I'm the Windows guy, I'll
have to explain this to the Linux guy!
In particular you'd want to use it to install the
unknown function from unknown.pltcl. After that, the unknown
function will be loaded automatically into new pltcl interpreters.
Is there a way to install my own unkown function, and to have it become
permanant with no startup required? I don't
if you want this to be for pltcl not pltclu... You're not going to be
able
to source random files from the filesystem.
Thanks - we're doing this using pltclu and have been using source... for 4
years with no trouble.
Well, you can use whatever unknown function you want, but it has to be
in this case UNION ALL would
do.
Carlo
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: August 12, 2010 6:48 PM
To: Carlo Stonebanks
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Very bad plan when using VIEW and IN (SELECT...*)
Carlo Stonebanks stonec.regis
Ref these two queries against a view:
-- QUERY 1, executes 0.5 secs
SELECT *
FROM mdx_core.vw_provider AS p
WHERE provider_id IN (13083101)
-- QUERY 2, executes 13.5 secs
SELECT *
FROM mdx_core.vw_provider AS p
WHERE provider_id IN (SELECT 13083101)
I am using the simple IN (SELECT n) to
Oops, my bad - this should be in PG PERFORM! Posting it there...
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi gang,
I wrote a PL/Tcl stored proc to do paging updates (we have very large
tables, and updates are these frustrating things that we stare at, not
knowing if they are in a deadlock, or progressing, or when they will
finish).
So, I wrote a tcl proc that runs like this:
SELECT
Obviously we need to improve our documentation. What led you to
believe it does not exist?
This is my fault entirely. When I Googled for this, I flailed around with
fancy terms that didn't connect. And, as you pointed out, its not in the
core distibution or the foundry. But I didn't consider
Is there any technical obstacle to anyone creating PL/PHP? I am cruious as
to why it doesn't alreay exist. I mean, I love my Tcl support, and I know
this is part of PG's legacy... but Tcl and no PHP? I figure there's a tech
reason for this - the demand must be there! No?
--
Sent via
The Windows builds (in our case, 8.3) still seem to be missing
magic tables: pltcl_modules and pltcl_modfuncs.
scripts: pltcl_loadmod, pltcl_listmod and pltcl_delmod
In 2006 I reported this problem (see below) with the PG 8.1 Windows build.
This issue came up, and I when I looked to see if it
to
Will that work?
Carlo
Tom Lane t...@sss.pgh.pa.us wrote in message
news:28281.1270071...@sss.pgh.pa.us...
Carlo Stonebanks stonec.regis...@sympatico.ca writes:
The Windows builds (in our case, 8.3) still seem to be missing
magic tables: pltcl_modules and pltcl_modfuncs.
scripts
(array_to_string(p.proallargtypes, ' ')::oidvector) in
this case ONLY.
6) The script does not generate any winning lottery numbers
Carlo
Tony Wasson ajwas...@gmail.com wrote in message
news:6d8daee31003221657h53f486d7r164b27587415e...@mail.gmail.com...
On Sat, Mar 20, 2010 at 11:09 PM, Carlo
Afaik no, you can make a schema-dump and extract the function
declarations from the dump.
Yeah, that's what I was doing. Bloody tedious. Thanks anyway!
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
in message
news:20100320081646.ga26...@tux...
Carlo Stonebanks stonec.regis...@sympatico.ca wrote:
I'd like to dump to text the full SQL required to create/replace all
user-defined functions within a specific schema - but JUST the function
declarations.
We are doing server migration
I'd like to dump to text the full SQL required to create/replace all
user-defined functions within a specific schema - but JUST the function
declarations.
We are doing server migration and there are some network paths in the code I
would like to search and replace.
Carlo
--
Sent via
PostgreSQL 8.3.6, compiled by Visual C++ build 1400
I see the log entries for 'loaded library
$libdir/plugins/plugin_debugger.dll'
Is there any cost or downside associated with this? There are suspicions the
server is quite the CPU hog.
--
Sent via pgsql-general mailing list
Now THIS is a new one for me! I have no idea where to even start. Does
anyone know how to look for the error? Below is the query and what I believe
are the related log entries.
Any help will be rewarded with heartfelt gratitude and praise, or you can
just come to Montreal and hit me up for
explicitly
used ALTER RENAME, though.
Sam Mason s...@samason.me.uk wrote in message
news:20091106170833.gx5...@samason.me.uk...
On Fri, Nov 06, 2009 at 11:09:23AM -0500, Carlo Stonebanks wrote:
This was really common with us with PG for years, and now it errors out -
what happened, and when
When I try the following command:
ALTER TABLE mdx_core.audit_impt RENAME TO _audit_impt;
I get the error message:
ERROR: type _audit_impt already exists
SQL state: 42710
I have looked through tables, types, sequences etc. I even did a PLAIN
schema (no data) backup on the DB and did a text
One of our developers asked me, is there any way to execute arbitrary
plpgsql? By that I beleive he means: is there some way to execute ad-hoc
pl/pgsql code without creating a stored procedure or a function?
I believe MS SQL Server can do this - has any one heard of some sort of
command shell
(FOR PG VERSION 8.3.6 running on Windows 2003 Server x64)
We have a function that assigns unique ID's (to use as row identifiers) to a
table via an UPDATE using nextval(). This table is imported from another
source, and there is a sequencing field to let the query know in which
order to
the data return in the FROM clause is ordered.
I also appreciate the time you took to re-write the query for me. Thank you
very much for taking the time to reply.
Carlo
Adam Rich ada...@sbcglobal.net wrote in message
news:49f748cd.9010...@sbcglobal.net...
Carlo Stonebanks wrote:
(FOR PG
Hermann Muster [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Hi,
I encountered something I can't really explain. I use the following
statement in my application:
COALESCE(UPPER(SUBSTR(Y.Firma,1,7)),'')
This returns ERROR: syntax error at end of input
However, using the
When performing a select ... into ... an app crashed out with the error
'duplicate key violates unique constraint pg_type_typname_nsp_index'
I looked in the mail lists and I see this error associated with TEMPORARY
tables, but the into table is not a temp table. A previous
You cannot block yourself with a lock, if that's what you mean.
The locks your session takes out will only block other sessions.
Well, that's the GOOD news! The bad news is - I'm not sure whether there's
somethign wrong with the logic of this query. The sub-query inside the FROM
is correct,
If I do an update using the FROM clause, and that clause has a sub-query
that refers to the table I am updating, will I be waiting for ever for a
table to lock to release?
The update before seems to stall, and it occurs to me that perhaps this is
the problem.
BTW - I rarely do anything but
When restoring a dump, I get the following error:
ERROR: could not make operator class gin__int_ops be default for type
pg_catalog.int4[]
DETAIL: Operator class _int4_ops already is the default.
I believe the problem lies with:
CREATE OPERATOR CLASS gin__int_ops
DEFAULT FOR TYPE
It means that measuring alters the reality.
Heisenberg would agree!
---(end of broadcast)---
TIP 6: explain analyze is your friend
Actually it's quite possible that
that extra overhead in nested loop is largely gettimeofday() calls for the
explain analyze. Does the query take less time run without explain analyze
than it does run with it?
Do you mean that an EXPLAIN ANALYZE that runs longer than the actual query
is a
I'm not entirely sure what Schrödinger would think about it.
I know what the cat thinks:
What do you mean 'no observer'? What the #*! am I??? LET ME OUT!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Joshua, where can I find docs on how to return SETOF from Tcl?
Joshua D. Drake [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
- Doing a lot of DB manipulation in pl/Perl or pl/Tcl or such
requires having an extra level of function manipulations that
won't be as natural as
The Windows-PosgreSQL 8.1.4 package I downloaded from the PostgreSQL
download site (right here) does not seem to contain the scripts:
pltcl_delmod
pltcl_listmod
pltcl_loadmod
Have I missed something, or can anyone else confirm?
Carlo
---(end of
for the scripts pltcl_loadmod, pltcl_listmod and pltcl_delmod mentioned in
the PostgreSQL documentation of the unknown command.
Carlo
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Ian
Harding
Sent: August 1, 2006 2:49 PM
To: Carlo Stonebanks
Cc: pgsql
(pltcl_modules pltcl_modfuncs), and why I don't have the
support scrtipts for them in my Windows installation.
Carlo
Tom Lane [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
Carlo Stonebanks [EMAIL PROTECTED] writes:
To enable this behavior, the PL/Tcl call handler must be compiled
plPHP is not as mature as plTcl (or is that plTclng). However it is very
well developed and maintained. Heck, companies are even holding talks and
training classes on it now.
What is lacking in plPHP? To be honest, even though I am a Tcl developer I
would rather develop in PHP, and I know
Yes - this statement actually came from a helpful soul who had replied but
did not realise I was talking about pltcl.
I am looking into the unknown command because I believe there may be
security issues with using spource or package commands to access proc
libraries - and that the unknown
If you don't see them, you should take it up with whoever created the
package you're using.
I thought that's what I was doing - I downloaded postgresql-8.1.4-1.zip from
http://www.postgresql.org/ftp/binary/v8.1.4/win32/
Is there somewhere else I should be directing my question?
Carlo
I have been trying to figure out where to put my plTcl
questions, and where the people most knowledgable about that topic may be
either on these mail lists or elsewhere.
Postgres docs makes reference to scripts called: pltcl_loadmod, pltcl_listmod, pltcl_delmod
but I cant find them
called it a Good Thing when Carlo Stonebanks
[EMAIL PROTECTED] wrote:
I am interested in finding out a non-religious answer to which
procedural language has the richest and most robust implementation
for Postgres. C is at the bottom of my list because of how much
damage runaway code can
From the plTcl docs it appears the way for me to create function/procedure
libraries for plTcl is by implementing the unknown command. However, my
8.1.4 Windows-based installation doesn't seem to jibe with what the docs say
I should expect::
PL/Tcl has a special support for things often
65 matches
Mail list logo