Hi,
I'm looking for a solution for indexing long TEXT columns. We're currently using a HASH index, which can handle most
situations, but every now and then we need support for even longer texts.
One solution would be to create a functional index which would only use the first N chars of
Hi,
from a table with 100,000,000 rows I have to sum some records using a
query like:
select sum(field1) from mytab where
(time = 1 and id = 3)
or
(time = 3 and id = 1)
or
(time = 2 and id = 5)
[...]
The or clauses can be even 10,000,000...
Which would be the best method to access data?
* Scara Maccai:
Which would be the best method to access data? Should I use a
procedure on the server side?
I tend to use a join to a temporary table for similar purposes. It
seems like the cleanest approach.
--
Florian Weimer[EMAIL PROTECTED]
BFK edv-consulting GmbH
Scara Maccai wrote:
Hi,
from a table with 100,000,000 rows I have to sum some records using a
query like:
select sum(field1) from mytab where
(time = 1 and id = 3)
or
(time = 3 and id = 1)
or
(time = 2 and id = 5)
The or clauses can be even 10,000,000...
Which would be the best method to
Put the test-values into a temporary table, analyse it and then join
against it.
Ok, I didn't think of it.
Can't say about indexes without knowing more about your
usage pattern.
What do you mean?
---(end of broadcast)---
TIP 2: Don't
Scara Maccai wrote:
Put the test-values into a temporary table, analyse it and then join
against it.
Ok, I didn't think of it.
Can't say about indexes without knowing more about your usage pattern.
What do you mean?
You might want an index on time, id, (id,time) or (time,id) - depends
On Tue, Jan 16, 2007 at 10:20:04AM +0900, Takayuki Tsunakawa wrote:
From: Magnus Hagander [EMAIL PROTECTED]
But yeah, that's probably a good idea. A quick look at the code says
we
should at least ask people who have this problem to give it a run
with
logging at DEBUG5 which should then
Hi,
I've been wondering with some wrong results that are
being returned from some functions in my application.
Basically, they are algebraic functions dealing with
int values.
Some results made me think of coercion between int
types. For example, atributing a int8 value into a
int2 variable.
Hello All,
I want to store count(*) of a table in a variable ,
for that I have declared a variable and wrote a
statment but it is giving me error.
can anybody please help me..
CREATE OR REPLACE FUNCTION foreign_keys_tables(OUT
par_result charecter varying, IN par_tablename
character varying , IN
On Tue, Jan 16, 2007 at 05:08:29AM -0800, Ashish Karalkar wrote:
Hello All,
I want to store count(*) of a table in a variable ,
for that I have declared a variable and wrote a
statment but it is giving me error.
can anybody please help me..
It would help immensly if you showed us the actual
Hi,
I have some perl code that I need to load dynamically in my postgres function.
How can this be accomplished?
I can do it in C using shared objects but don't know how would the same work
with perl.
Is there anything like shared objects in Perl or something.
Thanks,
Jas
I used shp2pgsql.exe to create an import sql for my gis database.
The resultant sql has data like this in it.INSERT INTO gis.sa_area
(label,type,level,the_geom) VALUES
('MÔRELIG','0x2','2','01060001000');
The Ô is ascii char 212.
This wont import, PSQL returns
ERROR: invalid byte
You treat it like any other perl code (you don't have to do
anything special just because it's in postgres):
If it's pure perl code, see require
http://perldoc.perl.org/functions/require.html
If it's a perl module, see use
http://perldoc.perl.org/functions/use.html
If you want to access a C
Simon Riggs wrote:
Perhaps we should focus on the issues that might result, so that we
address those before we spend time on the details of the user interface.
Can we deadlock or hang from running multiple autovacuums?
If you were to run multiple autovacuum processes the way they are today,
elein wrote:
Have you made any consideration of providing feedback on autovacuum to users?
Right now we don't even know what tables were vacuumed when and what was
reaped. This might actually be another topic.
I'd like to hear other people's opinions on Darcy Buskermolen proposal
to have a
Assuming you have pl/perl support compiled into PostgreSQL, just use
CREATE FUNCTION:
http://www.postgresql.org/docs/8.2/interactive/plperl-funcs.html
On Jan 16, 2007, at 9:24 AM, Jasbinder Singh Bali wrote:
Actually I want to load my perl code in postgres function.
How would i do that?
On Tue, Jan 16, 2007 at 03:40:52PM +0200, Gary Benade wrote:
I used shp2pgsql.exe to create an import sql for my gis database.
The resultant sql has data like this in it.INSERT INTO gis.sa_area
(label,type,level,the_geom) VALUES
('MÔRELIG','0x2','2','01060001000');
The Ô is ascii
Actually I want to load my perl code in postgres function.
How would i do that?
In C you have the notion of shared objects that you dynamically load
in postgres functions.
If one has to dynamically load the functionality of some perl code in
postgres function, how would that be done?
I think i
Oisin Glynn wrote:
From the Docs see below it implies that the WHERE clause of the update
is ignored.(
http://www.postgresql.org/docs/8.1/static/sql-createrule.html )
If I create a Rule as
CREATE RULE foo_update_false AS ON UPDATE TO foo
WHERE NEW.some_flag = TRUE
DO NOTIFY foo_update;
On Tuesday 16 January 2007 06:29, Alvaro Herrera wrote:
elein wrote:
Have you made any consideration of providing feedback on autovacuum to
users? Right now we don't even know what tables were vacuumed when and
what was reaped. This might actually be another topic.
I'd like to hear other
So there is nothing called dynamic loading of perl code in postgres.
I'll have to include the whole perl script in the postgres function you mean?
Thanks,
Jas
On 1/16/07, John DeSoi [EMAIL PROTECTED] wrote:
Assuming you have pl/perl support compiled into PostgreSQL, just use
CREATE FUNCTION:
Hello Richard,
I decreased the import time from 40 minutes to 32 minutes with changing
the parameters to:
shared_buffers = 45 # kept same
temp_buffers = 10# kept same
work_mem = 32768 # now 32mb , before: 102400 = 100mb
maintenance_work_mem =
Hi all,
I'm wondering if it is possible to connect a postgresql 8.2 server to a
datadir in readonly mode.
We actually want to implement a very large project, using postgresql database,
with pgpool. Our idea is, that one postgres instance, can read/write to the
files, while the other one should
On Tue, Jan 16, 2007 at 04:35:24PM +0100, Thorsten Körner wrote:
Hi all,
I'm wondering if it is possible to connect a postgresql 8.2 server to a
datadir in readonly mode.
We actually want to implement a very large project, using postgresql
database,
with pgpool. Our idea is, that one
Magnus Hagander [EMAIL PROTECTED] writes:
And actually, when I look at the API docs, our case now seems to be
documented. Or am I misreading our situation. I have:
If you call CreateFile on a file that is pending deletion as a result
of a previous call to DeleteFile, the function fails. The
On Tue, Jan 16, 2007 at 11:11:59AM -0500, Tom Lane wrote:
Magnus Hagander [EMAIL PROTECTED] writes:
And actually, when I look at the API docs, our case now seems to be
documented. Or am I misreading our situation. I have:
If you call CreateFile on a file that is pending deletion as a
That sounds perfect, but it doesn't seem to exist on either of the
postgresql installations I have access to (8.1 on ubuntu and fedora core).
Is it new to 8.2? Is there a similar function under 8.1, or at least a
decent work-around? Thanks for the help,
Doug
On 1/15/07, Teodor Sigaev [EMAIL
Hello!
I have written a function to load yahoo quote data. abut after parsing
the inserts will overload the server and disconnects. What sould I do
to avound this?
THX
Christian Maier
PS Here the function:
CREATE OR REPLACE FUNCTION get_yahoo(VARCHAR(20), BIGINT, VARCHAR(3))
RETURNS INTEGER AS
Hi!
I'm not sure about the English terminology for that so I'm sorry if I made a
mistake on the subject and on this message.
I've a table with 50 colums. I want to copy a certain row using PL and change
only 2 values. The way to do it with insert is to long. Is there any other
elegant way?
Tom Lane wrote:
Magnus Hagander [EMAIL PROTECTED] writes:
And actually, when I look at the API docs, our case now seems to be
documented. Or am I misreading our situation. I have:
If you call CreateFile on a file that is pending deletion as a result
of a previous call to
I have tried testing the perofmance on indexing array element using standard
btree and it doesn't help anything. It still costing alot.
create index idx_properties_address_4 on properties ((address_arr[4]))
does contrib/intarray effective for text array?
On 3/16/06, Oleg Bartunov
Christian Maier [EMAIL PROTECTED] writes:
I have written a function to load yahoo quote data. abut after parsing
the inserts will overload the server and disconnects.
You'll need to be a lot more specific than that. What error messages do
you see exactly? What shows up in the postmaster log?
2007/1/16, Max Ueda [EMAIL PROTECTED]:
Hi,
I've been wondering with some wrong results that are
being returned from some functions in my application.
Basically, they are algebraic functions dealing with
int values.
Some results made me think of coercion between int
types. For example,
Hi Filip,
The example occurs when I pass the parameter via
PEARDB . I made the mistake of setting the int4 type
for a variable that could assume int8 values. So, when
I passed, for example, a five digit integer, it made
the coercion and returned no error, and the function
had ran with the wrong
On 1/16/07, richard lavoie [EMAIL PROTECTED] wrote:
Hi!
I'm not sure about the English terminology for that so I'm sorry if I made a
mistake on the subject and on this message.
I've a table with 50 colums. I want to copy a certain row using PL and change
only 2 values. The way to do it with
Hello,
I have a table named foobar and I don't want to allow from DELETE or UPDATE
its rows.
I have a table as described below:
foobar(foobar_id, value, is_deleted);
I don't want to allow directly delete or modify the table's rows. I plan to
make an on before update or delete trigger and
on
On Mon, Jan 15, 2007 at 11:52:29 +0100,
Jan van der Weijde [EMAIL PROTECTED] wrote:
Does anyone have a suggestion for this problem ? Is there for instance
an alternative to LIMIT/OFFSET so that SELECT on large tables has a good
performance ?
Depending on exactly what you want to happen, you
Merlin Moncure [EMAIL PROTECTED] writes:
On 1/16/07, richard lavoie [EMAIL PROTECTED] wrote:
I've a table with 50 colums. I want to copy a certain row using PL and
change only 2 values. The way to do it with insert is to long. Is there any
other elegant way?
the basic methodology is to:
Greetings,
I am trying to work with a TEMP TABLE within a plpgsql function and I
was wondering if anyone can explain why the function below, which is
fine syntactically, will work as expected the first time it is called,
but will err out as shown on subsequent calls. The DROP TABLE line
seems to
On Tue, Jan 16, 2007 at 12:06:38 -0600,
Bruno Wolff III [EMAIL PROTECTED] wrote:
Depending on exactly what you want to happen, you may be able to continue
where you left off using a condition on the primary key, using the last
primary key value for a row that you have viewed, rather than
On Tue, Jan 16, 2007 at 11:10:25AM -0700, Lenorovitz, Joel wrote:
Greetings,
I am trying to work with a TEMP TABLE within a plpgsql function and I
was wondering if anyone can explain why the function below, which is
fine syntactically, will work as expected the first time it is called,
but
On Tuesday 16 January 2007 10:10, Lenorovitz, Joel
[EMAIL PROTECTED] wrote:
Greetings,
I am trying to work with a TEMP TABLE within a plpgsql function and I
was wondering if anyone can explain why the function below, which is
fine syntactically, will work as expected the first time it is
I wonder if anyone might help me generate a SQL query that peers
into pg_locks, pg_stat_activity, etc and tells in plain language
exactly *who* each backend is blocked *on* while awaiting
lock(s).
Here's what I'm looking at now:
SELECT date_trunc('second', now()) as now, a.client_addr as ip,
Hi Martijn,
thank you for your help.
Am Dienstag, 16. Januar 2007 16:45 schrieb Martijn van Oosterhout:
On Tue, Jan 16, 2007 at 04:35:24PM +0100, Thorsten Körner wrote:
Hi all,
I'm wondering if it is possible to connect a postgresql 8.2 server to a
[...]
Is there a way to set such
I assume your choices are to include the entire script in a
PostgreSQL function or simply create a small stub function that calls
a function you have loaded with use or require (as mentioned by the
other poster). But either way, you have to create a function so you
can tell PostgreSQL what
Furesz Peter wrote:
Hello,
I have a table named foobar and I don't want to allow from DELETE or
UPDATE
its rows.
I have a table as described below:
foobar(foobar_id, value, is_deleted);
I don't want to allow directly delete or modify the table's rows. I plan to
make an on before update or
On Mon, 2007-01-15 at 23:26, Tom Lane wrote:
carter ck [EMAIL PROTECTED] writes:
And you are definitely right when updating the table. The time it takes is
getting longer and longer. When I do a select statement, the speed has also
degraded.
Seems like you need a VACUUM in there
It appears that what is happening is PL/pgSQL is caching the table
definition (it appears to do this on first execution), testing it with
dynamic SQL via the EXECUTE clause doesn't exhibit the same issue:
CREATE OR REPLACE FUNCTION test_fxn() RETURNS SETOF RECORD AS $$
DECLARE
test_rec RECORD;
Alvaro Herrera wrote:
I'd like to hear other people's opinions on Darcy Buskermolen proposal
to have a log table, on which we'd register what did we run, at what
time, how long did it last, how many tuples did it clean, etc. I feel
having it on the regular text log is useful but it's not good
Hi,
please have a look at these introducing statements:
sandbox=# create table q(i integer, t text, primary key (i,t));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index q_pkey for
table q
CREATE TABLE
sandbox=# create table f(i integer, t text, foreign key (i,t)
On Tue, Jan 16, 2007 at 04:14:26 -0800,
Max Ueda [EMAIL PROTECTED] wrote:
Some results made me think of coercion between int
types. For example, atributing a int8 value into a
int2 variable. Does it really happen (coercion)? Is
the int8 value automatically converted into int2, or
an error
Bertram Scharpf [EMAIL PROTECTED] writes:
Is there a deeper reason why the foreign key allows not
referenced non-null values
The SQL spec says so. Use MATCH FULL to get the behavior you want.
regards, tom lane
---(end of
I was just wondering if one could use something like this
CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS $$
require abc.pl
$$ LANGUAGE plperl;
To include abc.pl here, how is the path of abc.pl specified. Also,
just wondering if the structure of above function is
On Tue, 16 Jan 2007, Bertram Scharpf wrote:
Hi,
please have a look at these introducing statements:
sandbox=# create table q(i integer, t text, primary key (i,t));
sandbox=# create table f(i integer, t text, foreign key (i,t) references q);
Now, this is surprising me:
sandbox=#
Firing up 8.2.1 I notice that sub-items in a view are optimized out if
they aren't being selected.
For example, select item1, item2 from a_view would take just as long
as select item1, item2, item3, item4 from a_view
This isn't usually a problem, but if item3 or item4 are significantly
more
Harpreet Dhaliwal [EMAIL PROTECTED] writes:
I was just wondering if one could use something like this
CREATE FUNCTION *funcname* (*argument-types*) RETURNS *return-type* AS $$
require abc.pl
$$ LANGUAGE plperl;
You'd have to use plperlu, since require isn't considered a trusted
Nathan Bell [EMAIL PROTECTED] writes:
Firing up 8.2.1 I notice that sub-items in a view are optimized out if
they aren't being selected.
You mean not optimized out, I suppose. Declare your function as
non-volatile if you want the optimizer to assume it's OK to discard.
Hi!
I've always used General Bits from the Varlena website as a source of
information and recommended it for friends worldwide for better using
PostgreSQL.
There's been a while since I could use the website for the last time because
it looks like Brazilian networks are blocked somewhere after
Yeah, I saw the not optimized out typo as soon as I hit send.
What if the item that is taking a long time isn't a function, but rather
a sub-select?
Can I set the sub-select to stable, or perhaps set the entire view to
non-volatile to achieve the same result?
If not, can I set the sub-select
Nathan Bell [EMAIL PROTECTED] writes:
What if the item that is taking a long time isn't a function, but rather
a sub-select?
The point is that the view won't be flattened if there are nonvolatile
functions in its SELECT list.
regards, tom lane
I wrote:
The point is that the view won't be flattened if there are nonvolatile
functions in its SELECT list.
Sheesh ... s/nonvolatile/volatile/ of course ... this thread seems
afflicted with getting-it-backward disease :-(
regards, tom lane
On Tue, Jan 16, 2007 at 02:55:08PM -0700, Nathan Bell wrote:
Yeah, I saw the not optimized out typo as soon as I hit send.
What if the item that is taking a long time isn't a function, but rather
a sub-select?
The planner should be able to see that the item is non-volatile itself.
It only
Bertram Scharpf wrote:
Hi,
please have a look at these introducing statements:
sandbox=# create table q(i integer, t text, primary key (i,t));
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index q_pkey for
table q
CREATE TABLE
sandbox=# create table f(i integer, t
Greetings all. I've been poking around the web and trying to track
the status of the GUID/UUID patches. In the todo list, that item is
not marked as going into 8.3, and I feel this is unfortunate.
Searching google for postgresql guid -guide comes up with around
200,000 hits. It's obviously
On 1/16/07, Gary Benade [EMAIL PROTECTED] wrote:
I used shp2pgsql.exe to create an import sql for my gis database.
The resultant sql has data like this in it.INSERT INTO gis.sa_area
(label,type,level,the_geom) VALUES
('MÔRELIG','0x2','2','01060001000');
The Ô is ascii char 212.
This
On 1/16/07, Thorsten Körner [EMAIL PROTECTED] wrote:
The projects Website is a very high traffic site, with about 500 Pi /
sec. we
are facing performance trouble, when using just one server.
Searches etc. we have implemented using lucene on our backend servers, to
keep
the database alive, but
so my syntax is correct? just wondering if there's some fundamental mistake
in it
~Harpreet
On 1/16/07, Tom Lane [EMAIL PROTECTED] wrote:
Harpreet Dhaliwal [EMAIL PROTECTED] writes:
I was just wondering if one could use something like this
CREATE FUNCTION *funcname* (*argument-types*)
I am running PostgreSQL 8.1.5 under Windows Server 2003 Standard
edition. All has been running for weeks now, don't know if it has been
restarted since we installed. Today we installed a program, Paradox
database, which required a restart, and now the PostgreSQL Server
service won't start with the
Robert,
Open Computer Management and find the postgres service.
There should be an item saying Run As. You want that to be
a non-privledged account. Typically, postgres asks you what account
to use when you install it. Just make sure that account has not been
granted administrative rights
On Tuesday 16 January 2007 05:47 pm, Patrick Earl wrote:
Greetings all. I've been poking around the web and trying to track
the status of the GUID/UUID patches. In the todo list, that item is
not marked as going into 8.3, and I feel this is unfortunate.
[...]
One thing that is quite
That -- and make sure your Secondary Logon service is running. Also, to
confirm, Log On As column in front of the pg service should mention the
postgres (low privileged) user.
A. Hayee
On 1/17/07, Adam Rich [EMAIL PROTECTED] wrote:
Robert,
Open Computer Management and find the postgres
On 1/16/07, David Lee Lambert [EMAIL PROTECTED] wrote:
The problem with a random UUID generator is: where do you get the random
numbers? However, there are really only two platforms to worry about:
POSIX (use libuuid) and Win32 (there is probably a Win32 function to
generate
it, since MS
On Tue, Jan 16, 2007 at 19:47:28 -0200,
Jorge Godoy [EMAIL PROTECTED] wrote:
There's been a while since I could use the website for the last time because
it looks like Brazilian networks are blocked somewhere after routers from
speakeasy.net (220.ge-3-0.er1.sfo1.speakeasy.net from this
Hi, all
I get a problem with the jdbc and timestamp data column.
My postgresql running on the Solaris 10 and version is 8.1.6.
The table looks like this,
create DOMAIN CURRENTTIME AS TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
create table RY_ArticleCollection (
ArtIDINT4
74 matches
Mail list logo
