On Tue, May 12, 2015 at 12:26:15AM +0800, Anil Menon wrote:
> manualscan=> select count(*) From msgtxt where msgid in (
> manualscan(> select msgid From courier where org_id=3
> manualscan(> )
> manualscan-> ;
> count
> ---
> 10225
> (1 row)
> manualscan=> select count(*) Fro
On Tue, May 12, 2015 at 04:07:52PM +0800, Anil Menon wrote:
> Thank you very much - looks like I will have to prefix all cols.
You should anyway.
Queries with unaliased columns make it impossible to analyze without
in-depth knowledge of the database.
Consider:
select c1, c2, c3, c4, c5
from t1 j
On Wed, Sep 09, 2015 at 07:51:48PM -0400, Melvin Davidson wrote:
> *O/S = Windows 10PostgreSQL 9.2.10, compiled by Visual C++ build 1600,
> 32-bit*
> http://www.postgresql.org/docs/9.1/interactive/sql-createtable.html
>
> and like_option is:
> { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | I
On Mon, Sep 21, 2015 at 11:55:23AM +0200, Alex Magnum wrote:
> Hello,
>
> I have a float array holding geo location information.
>
> geoloc
> ---
> {5.3443133704554,100.29457569122}
> {5.3885574294704,100.29601335526}
> {3.1654978750403,101.6091585
On Mon, Sep 28, 2015 at 08:54:54AM -0600, Scott Marlowe wrote:
> Look up WAL-E. It's works really well. We tried using OmniPITR and
> it's buggy and doesn't seem to get fixed very quickly (if at all).
Any examples? I'm developer of OmniPITR, and as far as I know there are
(currently) no unfixed bu
On Mon, Sep 28, 2015 at 12:53:37PM -0600, Scott Marlowe wrote:
> The issue was reported as omnipitr-cleanup is SLOOOW, so we run
> purgewal by hand, because the cleanup is so slow it can't keep up. But
> running it by hand is not supported.
>
> We fixed the problem though, we wrote out own script
On Tue, Oct 20, 2015 at 07:00:04AM -0700, pinker wrote:
> Maybe somebody have an idea how to escape text string for use in hstore
> column? I have tried $$ and quote_literal in audit trigger function, but
> still db won't let me pass values with // or ' to the hstore...INSERT FROM
> trigger functio
On Mon, May 30, 2016 at 11:05:17AM -0700, Jeff Janes wrote:
> So my theory is that you deleted a huge number of entries off from
> either end of the index, that transaction committed, and that commit
> became visible to all. Planning a mergejoin needs to dig through all
> those tuples to probe the
On Tue, Jul 12, 2016 at 10:23:24AM +0200, Bjørn T Johansen wrote:
> I am trying to move a small system from Oracle to PostgreSQL and
> I have come upon a sql that runs really slow compared to on the Oracle
> database and I am not able to interpret why this is slow.
I loaded your explain analyze to
On Mon, Aug 15, 2016 at 06:27:06PM +0500, Михаил wrote:
> I need to escape double quotes only:
> test=# select regexp_replace('"""{Performer,"Boomwacker ""a""
> Recording""}"""', '([^"])"{2}([^"])', '\1\"\2', 'g');
> regexp_replace
> ---
Hi,
we have rather uncommon case - DB with ~ 50GB of data, but this is
spread across ~ 8 tables.
Running pg_dump -Fd -jxx dumps in parallel, but only data, and MOST of
the time is spent on queries that run sequentially, and as far as I can
tell, get schema of tables, and sequence values.
This
On Mon, Aug 29, 2016 at 01:13:17PM -0300, Alvaro Herrera wrote:
> > > This happens on Pg 9.5. Are there any plans to make getting schema
> > > faster for such cases? Either by parallelization, or at least by getting
> > > schema for all tables "at once", and having pg_dump "sort it out",
> > > inst
On Wed, Sep 07, 2016 at 03:05:38PM +0200, Alexander Farber wrote:
> ERROR: syntax error at or near "in_until"
> LINE 69: ... banned_until = CURRENT_TIMESTAMP + INTERVAL in_until,
>^
> Is there please a better way here?
Why don'
On Thu, Sep 08, 2016 at 03:19:59PM +, PICCI Guillermo SNOOP wrote:
> Hi,
> we are trying to install pgaudit in order to check its funcionality, and we'd
> like to know if there is any available rpm to do this.
as far as quick google search shows, pgaudit is a tool from 2ndQuadrant,
a
Hi,
So, we have this situation, where there is cluster with 5 smallish
databases:
$ select oid, pg_database_size(oid) from pg_database;
oid | pg_database_size
---+--
1 | 6752440
12035 | 6760632
16428 | 59779475640
16427 |294947000
1203
On Thu, Sep 22, 2016 at 02:23:20PM +0200, Sylvain Marechal wrote:
> is there a way to monitor the size of the pg_xlog directory in SQL? The
Assuming you have superuser privileges, it will most likely work:
select sum( (f).size ) from (select pg_stat_file('pg_xlog/' || pg_ls_dir) as f
from pg_ls_
On Mon, Sep 26, 2016 at 08:22:11PM +0200, Alexander Farber wrote:
> ERROR: syntax error at or near "IF"
> LINE 11: IF LENGTH(TRIM(in_msg)) > 0 AND
> ^
of course it doesn't like it, because sql doesn't have "if" command.
If you want to use such syntax, you have to use plp
On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote:
> Hi all
>
> I am designing an inventory application, and I want to ensure that the stock
> level of any item cannot go negative.
>
> Instead of maintaining a running balance per item, I store the original
> quantities received in o
On Thu, Oct 27, 2016 at 04:43:55PM +0200, Marcin Giedz wrote:
> Hello all
>
> I'm wondering if there is any natural implementation of heartbeats in
> libpq library? We've been facing specially in firewall env
> occasionally session drops between client and server. Extending
> session timeout dire
On Thu, Nov 03, 2016 at 11:28:57AM +0100, Tom DalPozzo wrote:
> What am I missing?
David already explained, but you might want to read also:
https://www.depesz.com/2011/07/14/write-ahead-log-understanding-postgresql-conf-checkpoint_segments-checkpoint_timeout-checkpoint_warning/
depesz
--
The b
On Tue, Feb 16, 2016 at 09:41:18AM -0600, Suresh Raja wrote:
> I use the function name in the body of pgsql code of the same function. Is
> the function name set to any variable that i can easily reference. Now I'm
> hard coding the function name in the code.
> In the above Raise info i use selec
On Wed, Mar 23, 2016 at 03:27:45AM -0700, zh1029 wrote:
> Hi,
> we are using PostgreSQL 9.3.6 version and observe data file size is not
> decreased after we deleted records from the table. It looks quite abnormal.
> Is it as PostreSQL designed?
>
> DBTestPostgres=# select count (*) from test_da
On Mon, Apr 25, 2016 at 08:55:21AM -0500, Cal Heldenbrand wrote:
> The only outside tool it requires is lsof to determine the hostname of the
> remote socket. Otherwise it uses plain stuff like awk / sec and bash tools.
Why would you need lsof to get hostname for remote connection, when you
can u
hi,
we have following situation:
pg 9.3.11 on ubuntu.
we have master and slave.
the db is large-ish, but we're removing *most* of its data from all
across the tables, and lots of tables too.
while we're doing it, sometimes, we get LOTS of processes, but only on
slave, never on master, that spend l
On Sat, May 28, 2016 at 07:25:18AM +0200, Pavel Stehule wrote:
> It is looking like spinlock issue.
> try to look there by "perf top"
First results look like:
Samples: 64K of event 'cpu-clock', Event count (approx.): 2394094576
On Sat, May 28, 2016 at 07:46:52AM +0200, Pavel Stehule wrote:
> you should to install debug info - or compile with dubug symbols
Installed debug info, and the problem stopped.
Don't think it's related - it could be just timing. I'll report back
if/when the problem will re-appear.
Best regards,
On Sat, May 28, 2016 at 08:04:43AM +0200, Pavel Stehule wrote:
> > > you should to install debug info - or compile with dubug symbols
> > Installed debug info, and the problem stopped.
OK. ot he problem back.
Ps looked like this:
USERPID %CPU %MEMVSZ RSS TTY STAT START TIME
On Sat, May 28, 2016 at 10:32:15AM -0700, Jeff Janes wrote:
> > any clues on where to start diagnosing it?
>
> I'd start by using strace (with -y -ttt -T) on one of the processes
> and see what it is doing. A lot of IO, and one what file? A lot of
> semop's?
So, I did:
sudo strace -o bad.log -
On Sat, May 28, 2016 at 02:15:07PM -0400, Tom Lane wrote:
> hubert depesz lubaczewski writes:
> > Does that help us in any way?
>
> Not terribly. That confirms that the processes are contending for a
> spinlock, but we can't tell which one. Can you collect a few st
Hi,
up to parallel executions, when we had node in explain analyze showing
"loops=x" with x more than 1, it meant that the "actual time" had to be
multiplied by loops to get real time spent in a node.
For example, check step 13 in https://explain.depesz.com/s/gNBd
It shows time of 3ms, but loops
On 6/3/07, Andrej Kastrin <[EMAIL PROTECTED]> wrote:
||001||,||Classification||,||Economics||,||N||
||001||,||Classification||,||Trends||,||Y||
etc...
it looks like you should be able to read it using COPY command.
something like:
copy some_table from stdin with delimiter ',' csv quote '||';
On 6/7/07, Sergei Shelukhin <[EMAIL PROTECTED]> wrote:
Version is 8.1
The query I originally ran returned ~4-5 rows and had a lot of other
joins and filtering conditions prior to the join with the big table.
Is there any way to instruct postgres to do joins in the specific
order or smth?
make
you can modify log_line_prefix to contain database name.
depesz
On 6/11/07, Rikard Pavelic <[EMAIL PROTECTED]> wrote:
Hi!
I'm looking for recommendation for tracking DDL changes on
single database instance.
Currently I'm using pg_log to extract DDL changes, but those changes
are cluster wide
hi,
this query:
select 1 where '1'::text in (1::int8);
worked fine in 8.2:
# select version();
version
PostgreSQL 8.2.4 on i686-pc-linux-gnu, compiled by GCC
On 6/14/07, Tom Lane <[EMAIL PROTECTED]> wrote:
This is intentional --- implicit casts to text are gone. You should be
happy that the above now fails, because it's calling your attention to
the fact that you've got very ill-defined semantics there. Is the
thanks for clarification. actually
On 6/26/07, Rafal Pietrak <[EMAIL PROTECTED]> wrote:
Is there an SQL construct to get it?
select
distinct on (t1.id)
t1.*, t2.*
from
test t1
join test t2 on t2.id > t1.id
order by t1.id asc, t2.id asc
should do the trick.
depesz
--
http://www.depesz.com/ - nowy, lepszy depesz
On 6/26/07, 金星星 <[EMAIL PROTECTED]> wrote:
Are there any solutions based on PostgreSQL that can support
distributing partitions (horizontal fragmentations) across different
nodes. It doesn't need to support distributed transaction, since data
inconsistent is not a critical problem in my situatio
On 7/4/07, Joshua N Pritikin <[EMAIL PROTECTED]> wrote:
Please mention that in the documentation:
dont you think this is perfeclty clear?
"If you want to do something specific with columns, you may write your very
own trigger function using plpgsql or other procedural languages (but not
SQL,
On 7/3/07, Emi Lu <[EMAIL PROTECTED]> wrote:
Can I know how to get the date of each month's last Thursday please?
Query: select getDateBaseOnWeekday('2007-04-01', 'Last Thursday');
Result: 2007-04-26
you can easily do it without functions.
for example, this select:
SELECT
cast(d.date + i
On 7/4/07, Joshua N Pritikin <[EMAIL PROTECTED]> wrote:
From where are you quoting? I was quoting from:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch2-ref.html
i was quoting file
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html
or actual
On 7/9/07, Zlatko Matic <[EMAIL PROTECTED]> wrote:
Does plpgsql has something equivalent to plperl $_SHARED or plpythonu
global
dictionary GD?
no, but you can use some table to emulate this. or a temp table.
depesz
--
http://www.depesz.com/ - nowy, lepszy depesz
On Mon, Aug 06, 2007 at 04:44:29PM -0300, Ranieri Mazili wrote:
> 1) Can I use a function that will return a string in a where clause like
> bellow?
> 2) Can I use a function that will return a string to return the list of
> columns that I want to show like below?
not in sql. you can in pl/pgsql
On Mon, Aug 13, 2007 at 02:47:06PM +1000, Naz Gassiep wrote:
> When entering data into a timestamptz field, if no timezone is added
> does it assume you've entered a UTC time, or the time at the timezone
> set in the session with SET TIMEZONE, or the local system time ?
i dont understand - why d
On Mon, Aug 13, 2007 at 03:17:36PM +, jf wrote:
> Trying to implement some simple digest routines via UDFs and for whatever
> reason I get: ERROR: invalid memory alloc request size 4294967293 on
> PG_RETURN_TEXT_P(); any ideas what the issue is exactly?
just checking - you do realize that it'
On Mon, Aug 13, 2007 at 05:07:50PM +, jf wrote:
> my understanding was that pgcrypto was not compiled by default?
your function is also not compiled on default. but pgcrypto is at the
very least available by default (in sources, or in precompiled
packages).
> Furthermore, finding next to no d
On Mon, Aug 13, 2007 at 07:22:26PM +, jf wrote:
> understood, I appreciate the suggestion. In addition I couldn't find any
> documentation that told me how to install the functions in pgcrypto (do I
> need to CREATE FUNCTION for every function in there?), the README mentions
> a .sql file thats
On Mon, Aug 13, 2007 at 07:36:41PM +, jf wrote:
> > usually it's in: /share/postgresql/contrib/pgcrypto.sql
> > in the database you want to use pgcrypto functions, you simply run this
> > sql (as superuser), and that's all.
> theory# pwd
> /home/jf/postgresql-8.2.4
> theory# cd share
> bash: cd
hi,
i need something to distinguish two separate calls for some select.
i tried to use c functions GetCurrentTransactionId() and
GetCurrentCommandId(),
but there is a problem:
if i'll make plpgsql function, which uses GetCurrentTransactionId() and
GetCurrentCommandId() - getCurrentCommandId change
On Thu, Aug 16, 2007 at 09:14:24AM -0500, Decibel! wrote:
> Well of course, if you're running it in a separate command. If you run
> the function twice from one query I'd expect both to return the same.
no. if i run one query with function i get sifferend commandid's inside
the function.
example:
On Thu, Aug 16, 2007 at 11:20:30AM -0400, Tom Lane wrote:
> AFAIR, the only state that's guaranteed to work like that is
> statement_timestamp. Of course you have to worry whether your machine
> is fast enough to do more than one client interaction within whatever
> the clock resolution is.
i'll
On Fri, Aug 17, 2007 at 10:22:55PM +0800, Phoenix Kiula wrote:
> Wow, smartest advice of the day! Yes, a lot of our data in that column
> has dots and numbers (800,000 compared to 6 million), so I wanted to
> get only to the stuff that was pure alphabets, but just didn't think
> of how.
what i rea
On Fri, Aug 17, 2007 at 07:49:08PM +0800, Phoenix Kiula wrote:
> I have dropped all indexes/indicises on my table, except for the
> primary key. Still, when I run the query:
> UPDATE mytable SET mycolumn = lower(mycolumn);
can you please check this:
select count(*) from mytable;
select count(
On Fri, Aug 17, 2007 at 09:50:42PM +0800, Phoenix Kiula wrote:
> > How big is the actual table itself (in bytes).
> Where should I find this?
select pg_relation_size('mytable');
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http
On Mon, Aug 20, 2007 at 07:00:32PM -0500, D. Dante Lorenso wrote:
> Exactly what I was looking for. Looks like I need to make moves to get
> from 8.1 onto 8.2 ;-)
in any pg you should simply use select currval('sequence_name'); and be
happy with it.
depesz
--
quicksil1er: "postgres is excelle
On Wed, Aug 22, 2007 at 07:07:20PM +0200, Christian Schröder wrote:
> These are the current settings from the server configuration:
>shared_buffers = 3GB
this is *way* to much. i would suggest lowering it to 1gig *at most*.
>max memory size (kbytes, -m) 3441565
this looks like to
On Wed, Sep 05, 2007 at 11:15:43AM +0200, Stefan Schwarzer wrote:
> SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS
> y_2004, countries_view.name AS name
> FROM pop_total, countries_view
> LEFT JOIN tpes_total ON tpes_total.id = countries_view.id
> WHERE pop_total.y_2004
On Thu, Sep 06, 2007 at 11:08:02AM +0200, Alban Hertroys wrote:
> create index tmp_idx on table(number) where number != trim(number);
> analyze table;
> update table set number = trim(number) where number != trim(number);
dont use !=. use <>. != does something different, and in fact it is
not a re
On Thu, Sep 06, 2007 at 01:39:51PM +0200, Nis Jørgensen wrote:
> Rubbish. From the documentation:
hmm .. i'm sorry - i was *sure* about it because we were bitten by
something like this lately - apparently it was similiar but not the
same.
sorry again for misinformation.
depesz
--
quicksil1er:
On Fri, Sep 07, 2007 at 12:30:06PM +0200, Erwin Moller wrote:
> Any tips/sites?
check this:
http://www.postgresql.org/communityfiles/13.sxi
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie
On Mon, Sep 10, 2007 at 05:06:35PM -0700, Jason L. Buberel wrote:
> I am considering moving to date-based partitioned tables (each table =
> one month-year of data, for example). Before I go that far - is there
> any other tricks I can or should be using to speed up my bulk data loading?
did you
On Tue, Sep 11, 2007 at 08:02:34PM +0400, Dmitry Koterov wrote:
> So, it's completely magical for me why "Session pooling", "Transaction
> pooling" and "Statement pooling" options are exist (see
> https://developer.skype.com/SkypeGarage/DbProjects/PgBouncer). If pgbouncer
> is not a balancer, what
On Wed, Sep 12, 2007 at 01:56:13PM -0500, Josh Trutwin wrote:
> Or would you have to compare each field in OLD, NEW to see if
> anything actually changed?
you dont have to compare all columns (at least not in 8.2 and newer).
please take a look at
http://www.depesz.com/index.php/2007/09/08/avoiding
On Thu, Sep 13, 2007 at 09:59:30AM +0200, Ottavio Campana wrote:
> 1) pg_dump each day and run diff
it will become increasingly painful as the table size increases.
> 2) modify some triggers we use and store the information in another table
this is the best choice. you can use table_log extensio
On Thu, Sep 13, 2007 at 11:25:39AM +0200, Kai Behncke wrote:
> I want that the user xy (who is no superuser) can Update a systemtable with:
> UPDATE pg_catalog.pg_class SET reltriggers = 0;
why dont you simply alter table disable trigger?
depesz
--
quicksil1er: "postgres is excellent, but like
On Thu, Sep 13, 2007 at 01:06:11PM +0200, Kai Behncke wrote:
> Could you give me an example for that please?
> Thank you very much :-), Kai
i think i gave.
ok. again:
alter table some_table disable trigger all;
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly
On Fri, Sep 14, 2007 at 04:48:08PM +0300, Panagiotis Pediaditis wrote:
> Is there some way of locking all database tables in a transaction
> without knowing their names
> or even better just locking the entire database? I know this is bad
> tactics but there is a specific
> case where i need it.
i have this table:
# \d text_words
Table "public.text_words"
Column | Type |Modifiers
+-+-
id | integer | not null default nextval('text_words_id_seq'::regclass)
word
On Sat, Sep 15, 2007 at 11:09:39AM -0400, Tom Lane wrote:
> hubert depesz lubaczewski <[EMAIL PROTECTED]> writes:
> > index is created using text_pattern_ops so i will be able to use it in
> > 'where word like '...%''
> > but, it appears it is no
On Sat, Sep 15, 2007 at 11:48:19AM -0400, Tom Lane wrote:
> As of a couple years ago, the regular text = operator only yields true
> for bitwise-equal strings, so we could perhaps drop ~=~ and use = in its
> place. But I'd be worried about breaking existing queries that expect
> the strangely-name
hi,
our system is handling between 600 and 2000 transactions per second. all
of them are very small, very fast. typical query runs in under 1ms.
yes - sometimes we get queries that take longer than then should get.
simple check shows that we have a very visible pattern of
every-5-minutes peak.
in t
hi,
i just fetched newest 8.3 from cvs head, compiled, ran.
when i set logs to "stderr", and enter query with error, i get this
information in logs:
ERROR: subquery in FROM must have an alias
HINT: For example, FROM (SELECT ...) [AS] foo.
STATEMENT: select count(*) from (select x from q order by
On Tue, Oct 02, 2007 at 04:29:02PM +0200, Stefan Schwarzer wrote:
> If I want to find the "common smallest year" for two given variables
> (say, I have years 1970, 1971, 2005 for variable 1 (GDP) and
> 1980, 1981,... 2003) for variable 2 (Fish Catch) ). It should come up
> with 1980 for a
On Wed, Oct 03, 2007 at 11:47:26AM -0400, Jerry Sievers wrote:
> Question: Am I overlooking a simple way of doing this?
yes. use plpython or plperl to do the job.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.c
hi,
would it be possible for someone to add "last query" for
pg_stat_activity view?
there is a lot of cases that we have "idle in transaction" sitting for
long time, and since we dont log all queries it is next to impossible to
tell where in app code the problem lies.
it would be very useful to g
On Tue, Oct 16, 2007 at 11:16:46AM -0400, Tom Lane wrote:
> Turn on log_statement. I don't believe the feature you are asking for
> will be nearly as useful as you think. More than likely, what it
> will show you is something like "commit; begin" and you'll be little
> wiser than before. What yo
On Wed, Oct 31, 2007 at 08:01:41AM -0700, Craig White wrote:
> my script looks like this...
> (all I want is to get a list of the tables into a text file pg_tables)
everybody else showed some ways, but i'll ask a question:
i hope you're not treating it as a backup? bacause when you do it that
way
On Fri, Nov 02, 2007 at 10:04:06AM +0100, Christian Rengstl wrote:
> 1) Is it possible to refer to a column in a different table, ie
> entering a value in mytable.x should only be allowed if mytable2.y=1 for
> example? I know that it is possible to use triggers to do that, but I
> think adding a si
On Thu, Nov 01, 2007 at 08:03:08PM -0700, Craig White wrote:
> *** begin pg_table_dump.scr ***
> #/bin/sh
> #
> # Script to identify tables, backup schema and data separately and
> # then finally, vacuum each table
> #
> DB_NAME=MY_DB
> BACKUP_PATH=/home/backup/postgres/production
> MY_SCHEMA=publi
is it just a simple ommission, or am i missing something?
we have interval / float8 ( = interval), so i think that adding interval
/ interval ( = float8) should be possible.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://w
On Thu, Nov 08, 2007 at 10:50:39AM -0500, Tom Lane wrote:
> hubert depesz lubaczewski <[EMAIL PROTECTED]> writes:
> > we have interval / float8 ( = interval), so i think that adding interval
> > / interval ( = float8) should be possible.
> What would you define it to mean,
hi,
i wrote this function:
#v+
CREATE OR REPLACE FUNCTION test(TEXT) RETURNS bool language plperl as $$
return (shift =~ /[a-ząćęłńóśźżĄĆĘŁŃŚÓŹŻ0-9_-]+/i) || 0;
$$;
#v-
it's functioning it not really relevant.
important thing is, that the creation of it fails:
psql:z.sql:25: ERROR: creation of P
On Mon, Nov 12, 2007 at 02:19:21PM +0100, Patric Bechtel wrote:
> Just guess: We have bigint id's through the system, so I want to give
> the users the convenience of typing only the last 4-5 digits (which most
> of the time is enough). So the query we issue really is
> ... like "%$userinput"
> Mak
On Mon, Nov 12, 2007 at 11:26:09AM -0500, Tom Lane wrote:
> hubert depesz lubaczewski <[EMAIL PROTECTED]> writes:
> > on the other hand. while i know and understand why there can't be "="
> > operator for text and int, i think that "like" could be
On Mon, Nov 12, 2007 at 10:54:53AM -0500, Gauthier, Dave wrote:
> Is there a function that'll return the position of the last occurance of
> a char in a string?
> For Example, in the string 'abc/def/ghi' I want the position of the 2nd
> '/'.
# select length(substring('abc/def/ghi' from '^(.*/)'
On Mon, Nov 12, 2007 at 05:18:28PM -0500, Tom Lane wrote:
> regression=# select '00123'::text like '0%';
> ?column?
> --
> t
> (1 row)
> regression=# select '00123'::int4 like '0%';
> ?column?
> --
> f
> (1 row)
i think it's definitelly ok - '00123'::text is *not equal* to
'0
On Mon, Nov 12, 2007 at 03:11:50PM -0800, Sarah Dougherty wrote:
> To recap with an example, the query below works fine, but how do I add a
> series to it?
generate_series will not help with this.
try the sequence approach, or this:
http://www.depesz.com/index.php/2007/08/17/rownum-anyone-cumulat
On Wed, Nov 14, 2007 at 10:26:52AM -0500, Tom Lane wrote:
> That's a fairly ugly/messy way of doing it. If you're going to need a C
> function anyway, why not just do it directly? As in the attachment.
actually you dont have to do it in c.
alec pointed (in comments) that there already is stateme
On Tue, Jan 15, 2008 at 04:49:41PM -0600, Scott Marlowe wrote:
> This query is not capable of using an index on name, since you can't
> use an index with a like beginning with a %... So
actually you can. you just can't use index for like %something%, but it
can be solved using trigrams or another
hi,
what is the maximum amount of data to be written in checkpoint?
i always assumed this to be .
but some last tests show it to be much more. what am i missing?
best regards,
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http
On Mon, Jan 28, 2008 at 03:32:18AM +1100, Phil Rhoades wrote:
> SELECT count(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ;
> ERROR: column "cnt" does not exist
> LINE 1: ...ount(*) as cnt, name FRoM tst GROUP BY name HAVING cnt = 1 ;
having count(*) = 1;
depesz
--
quicksil1er: "postg
On Wed, Oct 27, 2010 at 11:21:43AM +0200, Marc Mamin wrote:
> Hello,
>
> I want to export a list of procedure definitions, which seems to be a
> hard nut to crack :-(
> A solution could be to use a combination of pg_dump and pg_restore, but
> this also requires some time investment.
> It would be
Hi
have strange situation - too many xlog files.
PostgreSQL 8.3.11 on i386-pc-solaris2.10, compiled by cc -Xa
config:
# select name, setting from pg_settings where name ~ 'checkpoint|wal' order
by 1;
name |setting
--+---
On Mon, Nov 01, 2010 at 07:22:50PM +0100, Cédric Villemain wrote:
> > as I understand, max number of xlog files in pg_xlog should be ( 1 + 2 *
> > checkpoint_segments ).
> (2 + checkpoint_completion_target) * checkpoint_segments + 1
> => 291
this is formula gave to me by rhodiumtoad on irc, but we
On Mon, Nov 01, 2010 at 07:18:22PM +0100, Filip Rembiałkowski wrote:
> 2010/11/1 hubert depesz lubaczewski :
>
> > as I understand, max number of xlog files in pg_xlog should be ( 1 + 2 *
> > checkpoint_segments ).
>
> why?
>
> for a server overloaded with R/W tra
On Mon, Nov 01, 2010 at 08:13:49PM +0100, Cédric Villemain wrote:
> 2010/11/1 hubert depesz lubaczewski :
> > On Mon, Nov 01, 2010 at 07:22:50PM +0100, Cédric Villemain wrote:
> >> > as I understand, max number of xlog files in pg_xlog should be ( 1 + 2 *
> >> &g
On Mon, Nov 01, 2010 at 08:18:24PM +0100, Cédric Villemain wrote:
> 2010/11/1 hubert depesz lubaczewski :
> > On Mon, Nov 01, 2010 at 07:18:22PM +0100, Filip Rembiałkowski wrote:
> >> 2010/11/1 hubert depesz lubaczewski :
> >>
> >> > as I understand, max nu
On Mon, Nov 01, 2010 at 08:31:10PM +0100, Cédric Villemain wrote:
> It should stick at a maximum of 3 * checkpoint_segments + 1, if it
> exceed it will remove the extra files after.
if you'd look at the graph you'd notice that it never goes down to 2n+1.
And really - so far I have not yet heard/se
On Thu, Nov 04, 2010 at 08:24:14PM -0400, Carlo Stonebanks wrote:
> 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 fro
On Mon, Nov 08, 2010 at 07:19:43PM +0100, Jakub Ouhrabka wrote:
> Hi,
>
> we have several instances of following error in server log:
>
> 2010-11-08 18:44:18 CET 5177 1 @ ERROR: out of memory
> 2010-11-08 18:44:18 CET 5177 2 @ DETAIL: Failed on request of size 16384.
>
> It's always the firs
On Mon, Nov 08, 2010 at 01:45:49PM -0500, akp geek wrote:
> Hi All -
>
> Can you please share your thoughts and help me ?
>
> 1. I have 4 ( T1, T2 , T3, T4 ) tables where I have the data from
> a transactional system
>
> 2. I have created one more table D1 to denor
1 - 100 of 552 matches
Mail list logo
