wrote:
Vyacheslav Kalinin v...@mgcp.com writes:
I just got my hands on mysql (5.0.something) and it does not cache the
scalar subquery result.
So... now I'm completely puzzled whether this is a bug, a desired result
or
just a loosely standardized thing.
It's loosely standardized.
AFAICS
I just got my hands on mysql (5.0.something) and it does not cache the
scalar subquery result.
So... now I'm completely puzzled whether this is a bug, a desired result or
just a loosely standardized thing.
Help anyone?
On Fri, Aug 27, 2010 at 5:41 PM, Vyacheslav Kalinin v...@mgcp.com wrote:
Hi
Hi,
Apparently scalar subquery when used as a part of SELECT statement and when
it does not depend on outer query columns
is executed only once per statement, e.g.:
postgres=# select i, (select random()) rand from generate_series(1, 3) i;
i | rand
---+---
1 |
Great, thank you!
I assume this one goes into 8.4.3, right?
2010/1/18 Teodor Sigaev teo...@sigaev.ru
Basically, I started testing prefix matching in FTS and got into
troubles. Self-contained example follows:
Thank you, fixed. The reason was in incorrect optimization of GIN scan: GIN
reuses
Hello,
Basically, I started testing prefix matching in FTS and got into troubles.
Self-contained example follows:
postgres=# select version();
version
With 8.4's analytic capabilities you can do this:
select * from (
select userid, data - lag(data) over (partition by userid order by data)
diff
from foo) q
where diff is not null;
On Tue, Nov 10, 2009 at 5:40 PM, A B gentosa...@gmail.com wrote:
Hello there!
I have a tablefoo(
Try
select 'a' = any( $${a,x}$$ ) ;
or
select 'a' = any( array['a','x'] ) ;
On Sat, Aug 8, 2009 at 3:08 PM, Jasen Betts ja...@xnet.co.nz wrote:
jasen=# select 'a' = any( $${'a','x'}$$ ) ;
?column?
--
f
(1 row)
I expect it to be true instead. what am I doing wrong?
On Thu, Jun 11, 2009 at 4:36 PM, Dimitri Fontaine dfonta...@hi-media.comwrote:
Vyacheslav Kalinin v...@mgcp.com writes:
$conn = pg_pconnect(dbname=foo);
Please reconsider and use plain pg_connect().
--
dim
Uh, I just copied/pasted that part from somewhere in PHP manual, personally
I
however the COPY
command requires superuser and I really don't want to open up the DB
from a remote system with Superuser access.
COPY FROM STDIN does not need superuser privileges.
On Tue, Jun 9, 2009 at 10:35 PM, Tory M Blue tmb...@gmail.com wrote:
Thanks guys, the problem with copy from or to is that it creates an
array and thus puts a load of stuff in memory, it's possible the file
will get huge and I can't take that memory hit.
I'll look again and see if I missed
Forgot about COPY command in my previous reply:
$conn = pg_pconnect(dbname=foo);
$fd = fopen('file.dat', 'r');
pg_query($conn, copy bar from stdin);
while (!feof($fd)) {
pg_put_line($conn, fgets($fd));
}
fclose($fd);
pg_put_line($conn, \\.\n);
pg_end_copy($conn);
On Mon, Jun 8, 2009 at 8:33 PM, Bill Moran wmo...@potentialtech.com wrote:
Perhaps you want to take an exclusive lock on the table? The operation
you describe seems to suggest that you'd want to guarantee exclusive
write access to the table.
Exclusive table lock is a bit excessive IMO.
You are fetching an Oracle LOB locator. I am not a guru of OCI let alone
JDBC but AFAIK you should create a LOB descriptor, bind it to the locator
and load the LOB data that you can insert into postgres.
They're just there to hold temporary tables (so that the names of such
tables don't conflict with any non-temporary tables). The reason they
seem to accumulate is we only create one when needed, and there's one
for each concurrently executing backend if it creates any temp tables.
Hm, do
Hello,
In our application we need a pattern based text search, that is if user
supplies 'foo bar' he receives only documents containing words that begin
with 'foo' or 'bar', including themselves (something similar to MySQL '+foo*
+bar*' text search query). Currently we use separate table for
On Tue, Aug 5, 2008 at 6:53 PM, Tom Lane [EMAIL PROTECTED] wrote:
There's support for prefix text search in CVS HEAD, but not in any
released version ...
regards, tom lane
Oh, does that mean it won't be released until 8.4?
Thanks for the reply, Tom.
After tracing through this I see that the problem is that we don't have
statistics for inheritance trees, and so you're getting a default
estimate for the selectivity of the join condition.
I might be wrong but I suspect that the inheritance is not the only reason
This is 8.3.0.
Here is the reproduce code:
create table contacts (
cid integer primary key,
pid integer not null,
cpid integer
);
create index ix_contacts_pid on contacts (pid);
create index ix_contacts_cpid on contacts (cpid);
create table pinfo (
pid integer,
constraint pk_pinfo
Hello,
Consider two tables:
contacts:
cid integer primary key,
pid integer not null,
cpid integer
...
pinfo:
pid integer,
...
pinfo is a parent table with two partitions pinfo_p00 and pinfo_p01, all
three have primary keys on pid and partitions have proper constraints
that
EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
('||vals||') RETURNING currval('''||seqname||''') INTO newid'
You probably were to do:
EXECUTE 'INSERT INTO '||tablename||' ('||fields||') VALUES
('||vals||') RETURNING currval('''||seqname||''')' INTO newid
Note where last quote
Hello,
Reading the manual recently I came across this: (
http://www.postgresql.org/docs/8.3/interactive/xfunc-volatility.html )
Because of the snapshotting behavior of MVCC (see Chapter
13http://www.postgresql.org/docs/8.3/interactive/mvcc.html)
a function containing only SELECT commands can
You might think it's likely, but in fact no inlining will happen
because you have a volatile argument that's used more than once inside
the function body. Inlining would change the results.
Yeah, right, apparently I overestimated chances of inilining, thanks for the
hint, Tom. In fact in my
Hello,
Case insensitive pattern matching gives strange results for non-ascii
character (such as UTF-8 encoded cyrillic letters):
test=# select 'б' ~* 'Б' ;
?column?
--
f
(1 row)
( 'б' and 'Б' are lower and upper case variants of cyrillic 'B')
at the same time:
test=# select 'б' ilike 'Б'
Hello,
It is often convenient to have columns of a table in certain order (as shown
by psql or most GUI database explorers, it also affects INSERT's
without columns specified behavior) so as to most significant columns
to
come first, semantically close columns to be grouped etc, while the columns
Consider the following case which is almost exact snapshot of part of our
scheme:
Table cities
Column| Type | Modifiers | Description
--++---+-
ficity_id| integer| not
Given that (type_id, group_id) is the PK of widgets it is possible to
avoid self-join:
select group_id from widgets
where type_id = A or type_id = B
group by group_id
having count(1) = 2;
---(end of broadcast)---
TIP 3: Have you checked our
Yes, I know, but the backend does not allow for a bigger buffer. Trying
to use a 80K (char[81920])buffer did not work and returns:
INFO: string-size : 48015
INFO: +++
server closed the connection unexpectedly
This probably means the server terminated
I am guessing that collation rules are not applied to bytea columns, so one
can compare text according to C locale anyway if he populates bytea column
or just does something like
select * from url order by byteain(textout(url))
Most implementations of md5 internally consist of 3 functions: md5_init -
which initializes internal context, md5_update - which accepts portions of
data and processes them and md5_final - which finalizes the hash and
releases the context. These roughly suit aggregate's internal functions
(SFUNC
Hello,
I would appreciate if someone explained me how exactly prepared parametrized
statements are planned, i.e. what kind of assumptions planner makes on param
values, selectivity, expected row count etc. that affect in particular
whether indexes will be used or not. For instance consider the
Hello,
I would appreciate if someone explained me how exactly prepared parametrized
statements are planned, i.e. what kind of assumptions planner makes on param
values, selectivity, expected row count etc. that affect in particular
whether indexes will be used or not. For instance consider the
On Jan 15, 2008 7:58 PM, Tom Lane [EMAIL PROTECTED] wrote:
If that really is the behavior you want, and not a typo
It is, most of parameters passed to a plpgsql function are in fact parts of
the filter and if certain filter item is null it is considered unknown and
we don't want it to affect the
SELECT-list output aliases name the *output* columns of the SELECT.
Those output columns are not available to the SELECT's computation
clauses
Then it's unclear how could the first query work
It is not possible to assign triggers to system tables hence it is
impossible to reference system table in FK constraint. Obviously, users that
don't exist in the database cannot modify anything, you can use
session_user and current_user functions for logging purposes, see
It needs to store the number of bits present as well
Couldn't that be reduced to 1 byte that'd say how many bits count in the
last byte?
Only in the sense that numeric also has to store some meta data as well
like
the weight and display precision.
Is it really necessary to store display
You could write a set of expressions that yield proper order by field
in one case and say null in another. Say we want order by columns
col1, col2 desc if the first function argument is '1' and by col3
desc, col1, col2 if it is '2', this can be achieved as:
select col1, col2, col3 from mytable
Isn't EXTRACT(dow FROM day) IN (0,6) always true thus making select
return a row every time?
On 11/10/07, rihad [EMAIL PROTECTED] wrote:
I've been reading the online docs, but... code like this somehow ends up
in an indefinite loop:
CREATE OR REPLACE FUNCTION foo() RETURNS int AS $$
If you know the index name then:
SELECT pg_get_indexdef('your_index_name'::regclass)
will do.
In case you want a full list of indices for a certain table:
SELECT c2.relname, pg_get_indexdef(i.indexrelid, 0, true), c2.reltablespace
FROM pg_catalog.pg_class c, pg_catalog.pg_class c2,
Postgres starts separate process for each connection and you can just `kill`
(not `kill -9`) the unwanted process. Or you can do `select
pg_cancel_backend(pid)` from another session.
Tom Lane wrote:
The fix is pretty easy if you need it now
Thanks for the update, Tom.
Another little issue with that function. I cannot make it
accept NULL as an argument. It won't normally accept NULL
reporting an appropriate error that it cannot determine
argument type, nor will it accept
40 matches
Mail list logo
