Hi,
I've submitted[1] some useful portions of my SQL editing hacks for
emacs. I generally use them for navigating PostgreSQL (dump) scripts,
but it works for other RDBMSes as well. I hope it would work for you as
well.
Regards.
[1] http://www.emacswiki.org/emacs/SQLSourceSearch
--
Sent via pg
On Mon, 22 Dec 2008, David Fetter writes:
> On Mon, Dec 22, 2008 at 10:07:21AM +0200, Volkan YAZICI wrote:
>> Hi,
>>
>> In the company, we're facing with serious disk space problems which
>> is not caused by PostgreSQL, but the nature of our data. Database
>>
Hi,
In the company, we're facing with serious disk space problems which is
not caused by PostgreSQL, but the nature of our data. Database sizes are
around 200-300GB, which is relatively not that much, but databases
require strict backup policies:
- Incremental backup for each day. (250GB)
- Full
On Fri, 03 Oct 2008, Joseph S <[EMAIL PROTECTED]> writes:
> Aren't static functions supposed to executed only once per transaction?
There is no such promise, that depends on the PL you prefer. For
instance, in PL/scheme[1] non-volatile (immutable) and non-SRF functions
are cached per (top) transac
Jason Long <[EMAIL PROTECTED]> writes:
> I need to set up master vs slave replication.
>
> My use case is quite simple. I need to back up a small but fairly
> complex(30 MB data, 175 tables) DB remotely over T1 and be able to
> switch to that if the main server fails. The switch can even be a
> s
On Sun, 21 Sep 2008, "Anton Belyaev" <[EMAIL PROTECTED]> writes:
> And the questions about population remain the same:
> How to avoid examination of all the towns in the rectangle knowing
> that we need only 10 biggest?
> Does population worth including into a (3D) point (In order to create
> a 3D
On Sun, 21 Sep 2008, "Anton Belyaev" <[EMAIL PROTECTED]> writes:
> SELECT * FROM towns where alt1 <= alt <= alt2 AND long1 <= long <=
> long2 ORDER BY population LIMIT 10;
You're absolutely on the wrong path. Don't try to implement a logic,
that has been implemented by PostgreSQL in the most possi
"Rob Richardson" <[EMAIL PROTECTED]> writes:
> So how do I specify an interval in a function?
Does this help?
([EMAIL PROTECTED]:5432/test) [2008-07-31 15:49:54]
# CREATE OR REPLACE FUNCTION time_cmp_with_1w_offset
(_l_ts timestamp, _r_ts timestamp)
RETURNS boolean AS $$
DECLARE
Hi,
On Wed, 02 Jul 2008, Tom Lane <[EMAIL PROTECTED]> writes:
> Are there any foreign keys referencing this table? If so, you're
> probably running out of memory for the list of pending trigger events
> (to verify that the FK constraint isn't violated by the delete).
>
> Allowing the triggers to
Hi,
We have an IBM System x3850 machine running on RHEL 4.5 Cluster Suite
with high-availability enabled. During a huge delete process, PostgreSQL
(8.3.1) exhausts available memory and receives an OOM kill.
$ /srv/usr/bin/psql -e -f
~/schemas/working/test_1_5_1_0-schema-delete-bogus-2-20080625
On Thu, 19 Jun 2008, Tom Lane <[EMAIL PROTECTED]> writes:
> Volkan YAZICI <[EMAIL PROTECTED]> writes:
>> # tail -n 2 /srv/pg/pg_log/2008-06-19_141725.log
>> migration_test postgres 2008-06-19 17:58:05.185 EEST LOG: duration:
>> 2315.420 ms statement: EXECU
On Thu, 19 Jun 2008, Tom Lane <[EMAIL PROTECTED]> writes:
> What PG version are you using? Since 8.2 log_duration should show
> parameter values.
I don't want to interrupt your work, but as far as I see from logs --
with the configurations I sent previously -- PostgreSQL doesn't log
parameter val
Hi,
While log_statements logs parameter values with the logged queries, I
cannot see parameter values logged for erronous queries and queries
catched by log_min_duration_statements.
Here are our logging settings:
# grep ^log postgresql.conf
logging_collector = on # Enable ca
On Thu, 5 Jun 2008, "James B. Byrne" <[EMAIL PROTECTED]> writes:
> The link http://openssi.org redirects to
> http://openssi.org/cgi-bin/view?page=openssi.html and the most recent
> (pre-)release is discussed here:
> http://sourceforge.net/forum/forum.php?forum_id=768341
Hrm... It didn't 3-4 days
On Wed, 4 Jun 2008, "Gurjeet Singh" <[EMAIL PROTECTED]> writes:
> If you search for OpenSSI Postgres, you hit this link:
>
> http://wiki.openssi.org/go/PostgreSQL_on_OpenSSI_enabled_Knoppix
>
> I have done this setup and small test on it about an year ago. The
> performance was horrible. I cannot
On Wed, 04 Jun 2008, Klint Gore <[EMAIL PROTECTED]> writes:
> postgres=# create table bar (foo int);
> CREATE TABLE
> postgres=# \e
> ERROR: relation "bar" already exists
> postgres=#
Not exactly like that, consider placing a "\c new_copy" line to your
script file.
>> \ef regex - Edit fun
Hi,
I have two feature proposals for psql:
1. After executing some set of commands from a file via "\e foo.sql",
pressing C-p or Up brings executed commands, instead of
"\e foo.sql". Shouldn't psql be bringing "\e foo.sql"?
2. It would be really neat to be able to issue
\et regex -
On Tue, 3 Jun 2008, "Roberts, Jon" <[EMAIL PROTECTED]> writes:
> PostgreSQL does not have either a shared disk or shared nothing
> architecture.
But there are some turn arounds for these obstacles:
- Using pgpool[1], sequoia[2], or similar tools[3] you can simulate a
"shared nothing" architectu
On Wed, 14 May 2008, Gerald Quimpo <[EMAIL PROTECTED]> writes:
> Not really :-). I was just looking at the simplest possible thing that could
> work. I've looked at versioned/temporal databases. But you probably
> can't go there since it definitely adds a lot of complexity to your app
> and quer
On Wed, 14 May 2008, Gerald Quimpo <[EMAIL PROTECTED]> writes:
> Instead of trying to update the row in place, insert the row again,
> but with the field you need to mangle (in your example, "plate",
> in my example below, "k") already mangled. this only works if
> the field you're mangling is
Hi,
I'm trying to fake DELETEs to a table using below methods:
CREATE OR REPLACE FUNCTION mobileunit_fake_delete() RETURNS trigger AS $$
BEGIN
UPDATE mobileunit
SET networkid = OLD.networkid + OLD.muid * 1000,
groupid = 146688,
plate = 'DELETED
Hi,
When I try to grant execution permissions of a function to a role,
PostgreSQL warns that "no privileges were granted"
(backend/catalog/aclchk.c:221).
test_1_5_0_0=> \df+ emove.emove_outgoingmessages_delete_for_commsrv
List of functions
-[ RECORD 1 ]---+
Hi,
On some corner cases our database servers starts to use from swap space
and that totally makes PostgreSQL (or any other I/O bound application)
to fall upon its knees. In our current structure, responsiveness has the
highest priority and thus it is ok for us to cancel queries at that
instant an
hubert depesz lubaczewski <[EMAIL PROTECTED]> writes:
> i think it would be better off not to limit some functionality for
> builtin functions. it would be much nicer to have the ability to hint
> planer about rowcount from function *in* the sql.
>
> something like:
>
> select i from generate_serie
On Thu, 10 Apr 2008, "Roberts, Jon" <[EMAIL PROTECTED]> writes:
> I am migrating an 8.3 database from Windows to Solaris. We are using
> pl/java and I went through the installation process for this on Windows.
>
> I'm building Solaris from the source and when running ./configure, I
> don't see a
Mike Ginsburg <[EMAIL PROTECTED]> writes:
> There is probably a really simple solution for this problem, but for
> the life of me I can't see to think of it. I have three tables
>
> --contains u/p for all users in the site
> TABLE users (user_id INT primary key, username VARCHAR(50), password TEXT
On Wed, 2 Apr 2008, "Andrus" <[EMAIL PROTECTED]> writes:
> create Document ( docdate date, docorder integer )
>
> I need update docorder column with numbers 1,2 in docdate date order
> Something like
>
> i = 1;
> UPDATE Document SET docorder = i++
> ORDER BY docdate;
CREATE SEQUENCE document_doc
On Sat, 5 Apr 2008, Pedro Doria Meunier <[EMAIL PROTECTED]> writes:
> The record being created has a column with an Id that has to be passed as an
> argument to the function that, in turn, fills the other column in the same
> record.
Doesn't BEFORE INSERT/UPDATE trigger solve this problem? (If n
On Sat, 5 Apr 2008, Pedro Doria Meunier <[EMAIL PROTECTED]> writes:
> Is it possible to have a table's column default value set to some form of
> select?
AFAIK, you cannot provide sub-selects in the default values of a
field. E.g.
CREATE TABLE foo (bar int DEFAULTS (SELECT ...), ...);
For thi
Michael Enke <[EMAIL PROTECTED]> writes:
> I need to know if multiple tables have (may have most probably)
> identical content.
That sounds me you should re-consider your database design. Having PK/FK
relations is the reason we divide common parts into separate tables to
avoid from data duplicatio
On Fri, 28 Mar 2008, Sam Mason <[EMAIL PROTECTED]> writes:
> On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:
>> The result in this case should be:
>>
>> id value order_field
>> 3 10 1
>> 5 12 2
>> 45 8
Yet another lame solution:
test=# SELECT max(id) AS
On Fri, 28 Mar 2008, Sam Mason <[EMAIL PROTECTED]> writes:
> On Fri, Mar 28, 2008 at 01:12:49PM +0100, Stanislav Raskin wrote:
>> The result in this case should be:
>>
>> id value order_field
>> 3 10 1
>> 5 12 2
>> 45 8
>
> SELECT id, value
> FROM (
> SELECT DISTINC
On Wed, 26 Mar 2008, sam <[EMAIL PROTECTED]> writes:
> Iam trying to update a database table with approx 45000 rows. Iam not
> updating all rows at a time. Iam updating 60 rows at a given time for
> example. and this is happening in a FOR LOOP. A function that has the
> update statements is called
On Wed, 26 Mar 2008, Zdeněk Kotala <[EMAIL PROTECTED]> writes:
> 1) What type of names do you prefer?
> ---
> b) new one with pg_ prefix - pg_createdb, pg_creteuser ...
OTOH,
> d) remove them - psql is the solution
> 2) How often do you use these tools?
>
On Wed, 19 Mar 2008, Alex Vinogradovs <[EMAIL PROTECTED]> writes:
> params[0] = DirectFunctionCall1(textin,
> CStringGetDatum(pstrdup("192.168.1.1")));
Should't you be using inet_in instead of textin?
Regards.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make change
On Wed, 19 Mar 2008, Ondřej Fafejta <[EMAIL PROTECTED]> writes:
> SELECT array_cat(ARRAY[1,2], ARRAY[2,3]);
> array_cat
> ---
> {1,2,2,3}
>
> I need to get result without duplicity!
> {1,2,3}
I don't know any builtin way of achieving such a thing. But you may want
to use intarray contrib m
On Sat, 15 Mar 2008, mark <[EMAIL PROTECTED]> writes:
> select * from users where session_key is not Null order by id offset OFFSET
> limit 300
>
> i want to go through the whole table... it gets really slow like
> greater than 5 minutes when the OFFSET is over 500,000..
Did you try your chance w
On Dec 09 12:52, Tomi N/A wrote:
> 2006/12/9, Martijn van Oosterhout :
> >Check the documentation for the details, but it's basically:
>
> I would have, but I don't know where the documentation is. The README
> in /usr/share/doc/postgresql-x.x.x/contrib doesn't mention it.
Google does. Search for
On Nov 27 11:59, Bret Schuhmacher wrote:
> I'm trying to return multiple rows from a function, but all I can get
> ...
>
> CREATE OR replace function getOnCallVol() RETURNS RECORD AS $$
You should return "SETOF record". See related section of the manual
about SRFs (Set Returning Fucntions).
Reg
On Oct 26 10:55, Ottavio Campana wrote:
> Erik Jones wrote:
> > Put them in arrays and pass them as the arguments. But, I would like to
> > know what function could ever need 65 arguments?
>
> Consider that I have to invoke the function from a ZSQL method in zope.
> Do you know if it works?
>
>
On Oct 26 10:03, Ottavio Campana wrote:
> I'm writing some stored procedures in pl/pgsql for a database using
> postgresql 7.4.7.
>
> I need to write a complex function with 65 arguments, but when I try to
> run it I get an error complaining that arguments can be up to 32.
>
> Is there a way to s
On Oct 22 02:33, Uwe C. Schroeder wrote:
> On Sunday 22 October 2006 12:32, Volkan YAZICI wrote:
> > If I were you, I'd ask psycopg2 developers to implement parameters that
> > are natively supported by PostgreSQL. With parameters, you won't mess up
> > with any esca
On Oct 20 05:07, [EMAIL PROTECTED] wrote:
> I'm concerned about whether the usual parameter escaping mechanism is
> enough in a LIKE or regular expression search.
>
> I run a recent Postgres version and use the Python connector psycopg2
> for a web application. I understand that if I always escap
On Sep 18 02:57, David Brain wrote:
> >Use COPY instead. (You can also prefer CURSORs in your client
> >applications. See related discussions.)
>
> Yes, but COPY isn't an option if I need to do:
>
> SELECT * from really_big_table where foo='zebra' order by createddate
>
> Which is what I r
On Sep 18 01:52, David Brain wrote:
> I had in interesting issue the other day while trying to generate
> delimited files from a query in psql, using:
>
> \f'|'
> \t
> \a
> \o out.file
> select * from really_big_table sort by createddate;
>
> This quantity of data involved here is fairly large (
On Aug 10 07:57, Roger Mason wrote:
> run = "S90040"
> curs.execute("create table %s (date text, tset text)",(run,))
Please see this[1] and this[2].
[1] http://archives.postgresql.org/pgsql-general/2006-07/msg00706.php
[2] http://archives.postgresql.org/pgsql-general/2006-07/msg00707.php
Regard
On Jul 31 10:40, Parthan SR wrote:
> On 7/31/06, Richard Huxton wrote:
> Traceback (most recent call last): File
> "/usr/lib/cgi-bin/ConfSachem/page2.py", line 75, in ? main(num_days) File
> "/usr/lib/cgi-bin/ConfSachem/page2.py", line 68, in main query =
> cursor.execute('INSERT INTO ConfMain (Co
On Jul 21 04:25, Martijn van Oosterhout wrote:
> On Fri, Jul 21, 2006 at 05:07:08PM +0300, Volkan YAZICI wrote:
> > AFAICS, that's not possible with current parsing capabilities. See
> > related lines in
> >
> > fe-protocol3.c:pqParseInput3()
> > 102
On Jul 21 03:34, Martijn van Oosterhout wrote:
> Really, I would have thought the PHP function would map directly to the
> libpq PQftype() function. Although libpq returns the OID whereas the
> PHP function returns the type. But I don't think that's what the
> original user asked for given you need
On Jul 21 09:02, Jacob Coby wrote:
> Volkan YAZICI wrote:
> >As Tom mentioned, there's support for this feature in the protocol
> >level, but I don't know any API supports this yet. However, here's a
> >patch that adds Describe functionality for Prepared Stateme
On Jul 20 08:21, Dan Strömberg wrote:
> I would like to know if it is possible to find out the datatypes in the
> resultset of a query or a set returning
> function without actually executing them ?
As Tom mentioned, there's support for this feature in the protocol
level, but I don't know any API
On Jul 19 10:40, Timothy Smith wrote:
> Volkan YAZICI wrote:
> >On Jul 19 10:02, Timothy Smith wrote:
> >
> >>cur.execute("""CREATE USER %s WITH PASSWORD %s IN GROUP %s
> >>""",(StaffUserName,NewPassword,StaffGroup))
> &
On Jul 19 10:02, Timothy Smith wrote:
> cur.execute("""CREATE USER %s WITH PASSWORD %s IN GROUP %s
> """,(StaffUserName,NewPassword,StaffGroup))
>
> produces this error
>
> ERROR: syntax error at or near "'bob.smith'" at character 13
IIRC, per Python DB-API PEP, the DB adapter you use try to e
On Jul 13 08:28, Claire McLister wrote:
> Have you considered using a set instead? We had a similar need and
> were using an array as a parameter. That turned out to be taking too
> long. Recently we have changed it to a set and it seems to work
> faster, although I must admit I haven't timed
On May 25 11:04, Richard Huxton wrote:
> surabhi.ahuja wrote:
> >hi,
> >i am getting the following error messages:
> >
> ><2006-05-24 15:28:08 CDT%>LOG: could not bind IPv4 socket: Address
> >already in use
> ><2006-05-24 15:28:08 CDT%>HINT: Is another postmaster already running on
> >port 543
On May 23 11:30, [EMAIL PROTECTED] wrote:
> I'm currently trying to understand how to deal with the return values
> of PGresultStatus in terms of error handling in my application. The
> postgres manual describes the return codes of PGresultStatus as:
>
> PGRES_EMPTY_QUERY: The string sent to the
On Sat, May 06, 2006 at 12:08:32AM -0300, Marcelo Fabiano da Silveira wrote:
> I Have some questions of use PQtrace in Windows' systens
>
> 1- The implementation of PQtrace, is possible ONLY with non bloking
> connections ???
> 2- Please, I need same samples of implementation of PQtrace.
PQt
Hi,
[I'm trying to share some of my thoughts about intarray contrib module.
If this is the wrong way to achieve this, please warn me. (Should I
first get in touch with Teodor Sigaev and Oleg Bartunov?)]
[6]
_int_same() in _int_op.c looks like making some redundant sorting and
not taking advantage
Hi,
I've prepared a Quick & Dirty patch serie for some missing parts in
intarray contrib module. Here they go with some explanation...
On May 06 12:38, Volkan YAZICI wrote:
> [4]
> In the inner_int_contains() function of _int_tool.c, there's a while
> loop like
>
&
On May 06 05:38, Volkan YAZICI wrote:
> g_int_compress():
> if (integer array length > constant limit)
> {
> Transfrom {A, B, C, ..., Z} array into
> {A, A, B, B, ..., Z, Z}
>
> while (integer array length > constant limit)
> {
> Selec
Hi,
First, thanks so much for your response.
On May 06 12:13, Martijn van Oosterhout wrote:
> On Sat, May 06, 2006 at 12:46:01AM +0300, Volkan YAZICI wrote:
> > [1]
> > What's the function of execute() in _int_bool.c? As far as I can
> > understand, some other fun
On May 06 12:46, Volkan YAZICI wrote:
> I'm reading through the source code of intarray contrib module. Despite
> being at the beginning, I've some questions to ask. I'd be so
> appreciated if anybody can help.
Sorry, I forgot one:
[4]
In the inner_int_contains() function
Hi,
I'm reading through the source code of intarray contrib module. Despite
being at the beginning, I've some questions to ask. I'd be so
appreciated if anybody can help.
[1]
What's the function of execute() in _int_bool.c? As far as I can
understand, some other functions (eg. execconsistent())
On Feb 27 03:10, Michael Fuhr wrote:
> PostGIS has geometry accessors that might work. You'd need to be
> using PostGIS geometry types instead of the PostgreSQL types.
>
> http://postgis.refractions.net/docs/ch06.html
>
> Are the following examples anything like what the user in tr-general
> was
Hi,
2 weeks ago, a user in -tr-genel asked for a function to break
path/polygon type data into pieces. He also told that, it creates a
bottleneck in the network traffic when they try to receive rows with
polygon data of thousands of nodes, while it's enough for them to
have polygons partially.
AF
On Jan 18 09:00, Eric Davies wrote:
> Back in the days of 7.4.2, we tried storing large blobs (1GB+) in
> postgres but found them too slow because the blob was being chopped
> into 2K rows stored in some other table.
> However, it has occurred to us that if it was possible to configure
> the se
On Jan 10 03:56, MG wrote:
> I noticed that the phpPgAdmin has that information
>
> Name Last value Increment by Max value Min value Cache value Log count
> Is cycled? Is called?
> adr_dsnr 108 1 9223372036854775807 1 1 25 No Yes
test=# \d roomsold_main_id_seq
Sequence "public.room
Hi,
Here's a modified version of A. Kretschmer's answer. This one checks
array_upper() sizes and depending on it, doesn't provide unnecessary
NULL fields. HTH.
SELECT id, val[s.i]
FROM t7
LEFT JOIN
(SELECT g.s
FROM generate_series(1,
(SELECT max(arr
On Dec 12 08:09, Volkan YAZICI wrote:
> On Dec 12 01:00, David Rysdam wrote:
> > I have a table that has a date field and a "real" field that represents
> > a number of seconds. I want select the date field + the seconds field.
> > Just adding it doesn't wo
On Dec 12 01:00, David Rysdam wrote:
> I have a table that has a date field and a "real" field that represents
> a number of seconds. I want select the date field + the seconds field.
> Just adding it doesn't work. Casting to interval doesn't work.
> to_date/to_timestamp don't work. How do I
On Dec 07 06:56, Alexander Scholz wrote:
> I SELECTed them, that's right, but I didn't FETCHed them. I thought that
> would be a difference. It's at least the way it works with MSSQL and
> ODBC/OLEDB (that's where I am coming from...)
IMHO, you can reach to a similar functionaly with using async
On Dec 07 06:36, Alexander Scholz wrote:
> sorry for probably asking such a stupid question, but we are using a
> PQexec(). Where should I specify that cursor declaration?
Just like as you're querying with pgAdmin:
PQexec(conn, "BEGIN");
PQexec(conn, "DECLARE \"test\" CURSOR FOR SELECT * FROM \"
On Dec 07 06:01, Alexander Scholz wrote:
> I have a serious performance flaw, when using postgresql 8.1 (other
> versions haven't been tested) with libpq. When executing a
>
>select * from "xyz" 1)
>
> and "xyz" contains 300'000 records, it takes more than 60 seconds for
> the query just
On Dec 07 12:34, A.j. Langereis wrote:
> I have two questions: fist of all, is there any function in pg like
> oracle's rownum?
[Does Postgresql have a similar pseudo-column "ROWNUM" as Oracle?]
http://archives.postgresql.org/pgsql-sql/2005-05/msg00123.php
--
"We are the middle children of histo
On 12/5/05, Tom Lane <[EMAIL PROTECTED]> wrote:
> In plpgsql, you're supposed to use exception blocks instead.
AFAIC, SAVEPOINT usage isn't permitted in any procedural language. But
it also isn't documented. (I couldn't find any phrase mentioning about
this in the docs.)
One more question, if we'
On 12/1/05, marcelo Cortez <[EMAIL PROTECTED]> wrote:
> i have problems with encodings
PostgreSQL case conversion functions is a little bit buggy.
(Especially for Latin-N and Unicode encodings.) I've prepared a patch
[1] to fix a similar problem for Latin5 encoding. It wasn't tested so
much but w
Hi,
On 6/13/05, Zlatko Matić <[EMAIL PROTECTED]> wrote:
> I want to create VBA procedure that compares value in text box with actual
> password to determine if it is the same. As the password is encrypted (md5)
> I can't read it from pg_shaddow system table. How to retrieve decrypted
> value from
77 matches
Mail list logo
