Thread: Warning during pg_dump

Hi everyone,

when i issued the following command: pg_dump -d mydb -Fc -Z9 -U dbadmin > base_backup.tar
i keep getting warning messages: pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell
used.
Do i have to be worried about that, i.e. can it happen that the backup did not work? Mainly because my db is around
100GBand the tar file was just 4.5GB. I mean, i don't mind such a good compression but with the warning messages i got,
iam a little bit confused as to whether it really worked. 

Thanks!
Chris

Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230

"Christian Rengstl" <Christian.Rengstl@klinik.uni-regensburg.de> writes:
> when i issued the following command: pg_dump -d mydb -Fc -Z9 -U dbadmin > base_backup.tar
> i keep getting warning messages: pg_dump: [custom archiver] WARNING: ftell mismatch with expected position -- ftell
used.

This is definitely not very cool.  What platform are you on exactly
(particularly, libc or glibc version), and what PG version is this?
Can you make up a self-contained test case --- perhaps a script that
fills a database with junk data that will produce the problem when
dumped?

            regards, tom lane

Hi all,

I have a small coding problem where my function is becoming, well, too
ugly for comfort. I haven't finished it but you will get picture below.

First a small description of the purpose. I have an aggregate function
that takes a string and simply concatenates that string to the previous
(internal state) value of the aggregate, example:

"Hello:World" || ", " || "World:Hello" --> "Hello:World, World:Hello"

My problem is that I sometimes get the same value before the colon
sign and in those cases I should not add the whole string to the previous
value of the aggregate but extract the value that is behind the colon and
add it to already existing part which matched the value before the colon
but with a slash as a delimiter, example:

Internal state: "Hello:World, World:Hello"
New value: "Hello:Dolly"
After function is run: "Hello:World/Dolly, World:Hello"

So what I am doing is a lot of strpos() and substr() functions (I have
previously asked for the speed of the substr() function) but it is
beginning to look really alwful.

It seems very odd that there doesn't exist something else like what I need
but I haven't found anything, although I admit I might not understand all
aspects of the PostGreSQL database and what I can do with the SQL in
connection to it.

Below you will find my unfinished function, but it will show you what I
mean when I say ugly..

Any help is appreciated.

Thanks in advance,

Archie


CREATE FUNCTION rarity_concat(text, text)
   RETURNS text
   AS
     'DECLARE
        colon_pos integer;
        set_str text;
        rarity_str text;
        set_exist_pos integer;
        rarity_exist_str_middle text;
        rarity_exist_str_end text;
      BEGIN
        colon_pos := strpos($2, ':');
        set_str := substr($2, 1, colon_pos);
        set_exist_pos := strpos($1, set_str);
        IF set_exist_pos > 0 THEN
          rarity_str := substr($2, colon_pos + 2);
          rarity_exist_str_start := substr($1, 1, set_exist_pos - 1);
          comma_pos :=
        ELSE
           RETURN $1 || \', \' || $2;
        END IF;
      END'
   LANGUAGE 'plpgsql';

Well, first off, this would be much easier in one of the other pl's such
as for perl or ruby.  Using plpgsql, I would suggest using more of the
string function split_part since you know the delimiters the string can
split on, using str_pos just to verify that there is say a '/' in a part
of the string.  Or, you could use the substring function (regex) to pull
out portions to process (this is where plperl and plruby would work much
better).  Then just loop through, continually splitting the string down
into it's component parts, maybe placing the component parts into arrays
to preserve the relationships, then do your checks for equality against
the "keys" that you've split up and rebuild the whole string from the
ground up.

arsi@aranzo.netg.se wrote:
>
> Hi all,
>
> I have a small coding problem where my function is becoming, well, too
> ugly for comfort. I haven't finished it but you will get picture below.
>
> First a small description of the purpose. I have an aggregate function
> that takes a string and simply concatenates that string to the
> previous (internal state) value of the aggregate, example:
>
> "Hello:World" || ", " || "World:Hello" --> "Hello:World, World:Hello"
>
> My problem is that I sometimes get the same value before the colon
> sign and in those cases I should not add the whole string to the
> previous value of the aggregate but extract the value that is behind
> the colon and add it to already existing part which matched the value
> before the colon but with a slash as a delimiter, example:
>
> Internal state: "Hello:World, World:Hello" New value: "Hello:Dolly"
> After function is run: "Hello:World/Dolly, World:Hello"
>
> So what I am doing is a lot of strpos() and substr() functions (I have
> previously asked for the speed of the substr() function) but it is
> beginning to look really alwful.
>
> It seems very odd that there doesn't exist something else like what I
> need but I haven't found anything, although I admit I might not
> understand all aspects of the PostGreSQL database and what I can do
> with the SQL in connection to it.
>
> Below you will find my unfinished function, but it will show you what
> I mean when I say ugly..
>
> Any help is appreciated.
>
> Thanks in advance,
>
> Archie
>
>
> CREATE FUNCTION rarity_concat(text, text)
>   RETURNS text
>   AS
>     'DECLARE
>        colon_pos integer;
>        set_str text;
>        rarity_str text;
>        set_exist_pos integer;
>        rarity_exist_str_middle text;
>        rarity_exist_str_end text;
>      BEGIN
>        colon_pos := strpos($2, ':');
>        set_str := substr($2, 1, colon_pos);
>        set_exist_pos := strpos($1, set_str);
>        IF set_exist_pos > 0 THEN
>          rarity_str := substr($2, colon_pos + 2);
>          rarity_exist_str_start := substr($1, 1, set_exist_pos - 1);
>          comma_pos :=
>        ELSE
>           RETURN $1 || \', \' || $2;
>        END IF;
>      END'
>   LANGUAGE 'plpgsql';
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match


--
erik jones <erik@myemma.com>
software development
emma(r)

On 2006-10-03, arsi@aranzo.netg.se <arsi@aranzo.netg.se> wrote:
> Hi all,
>
> I have a small coding problem where my function is becoming, well, too
> ugly for comfort. I haven't finished it but you will get picture below.
>
> First a small description of the purpose. I have an aggregate function
> that takes a string and simply concatenates that string to the previous
> (internal state) value of the aggregate, example:
>
> "Hello:World" || ", " || "World:Hello" --> "Hello:World, World:Hello"
>
> My problem is that I sometimes get the same value before the colon
> sign and in those cases I should not add the whole string to the previous
> value of the aggregate but extract the value that is behind the colon and
> add it to already existing part which matched the value before the colon
> but with a slash as a delimiter, example:
>
> Internal state: "Hello:World, World:Hello"
> New value: "Hello:Dolly"
> After function is run: "Hello:World/Dolly, World:Hello"
>
> So what I am doing is a lot of strpos() and substr() functions (I have
> previously asked for the speed of the substr() function) but it is
> beginning to look really alwful.

You might have better luck with a different approach. For example, start
by accumulating the values into an array, rather than a string, and rather
than try and do the magic bits in the transition function, do them in one
pass at the end, making use of the full power of SQL queries rather than
trying to do your own procedural logic.

If you think about your problem in SQL terms, what you're really trying to
do is essentially a "group by" on your first field. If you can avoid the
need to pass that in as a colon-delimited value, then your life will be
much simpler; but even if you can't avoid that, the SQLish solution will
be easier.

As a sample of what you can do, here is a function that does part of the
job (requires the array_accum aggregate as given as an example in the
manual):

create function foo(text[]) returns text[] language sql immutable
as $f$
  select ARRAY(select k || ':' || array_to_string(v,'/')
                 from (select split_part($1[i],':',1) as k,
                              array_accum(substring($1[i] from ':(.*)')) as v
                         from generate_series(array_lower($1,1),
                                              array_upper($1,1)) s(i)
                        group by k) s1)
$f$;

=> select array_to_string(foo(ARRAY['foo:bar', 'baz:quux', 'foo:baz']),',');
   array_to_string
----------------------
 baz:quux,foo:bar/baz
(1 row)

To understand the function, look at the subqueries from the inside out;
the inner one splits the foo:bar elements into two columns, groups by the
first and collects the corresponding values into an array; the outer one
converts the format back to the one you require.

As a bonus, if you want to eliminate duplicate values, you can just add
the "distinct" keyword inside the array_accum aggregate.

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services

Hello Archie,

We approach the problem slightly differently then others.  Given an aggregate
function comma_list which simply creates a comma seperated list, we use
distinct to remove duplicates.

test=# select comma_list(col) from test;
 comma_list
------------
 a, b, a, c
(1 row)

test=# select comma_list(distinct col) from test;
 comma_list
------------
 a, b, c
(1 row)

I've included our function definitions below.

hope that helps,

-Chris

CREATE OR REPLACE FUNCTION list_add(text, text)
  RETURNS text AS
$BODY$
select
  CASE WHEN $2 IS NULL OR $2 ='' THEN $1
       WHEN $1 IS NULL or $1 = '' THEN $2
  ELSE $1 || ', ' || $2
END;
$BODY$
  LANGUAGE 'sql' VOLATILE;

CREATE OR REPLACE FUNCTION list_fin(text)
  RETURNS text AS
$BODY$
SELECT CASE WHEN $1=text('') THEN NULL
ELSE $1 END
$BODY$
  LANGUAGE 'sql' VOLATILE;

CREATE AGGREGATE comma_list(
  BASETYPE=text,
  SFUNC=list_add,
  STYPE=text,
  FINALFUNC=list_fin
);



On Tuesday 03 October 2006 03:26 pm, arsi@aranzo.netg.se wrote:
> Hi all,
>
> I have a small coding problem where my function is becoming, well, too
> ugly for comfort. I haven't finished it but you will get picture below.
>
> First a small description of the purpose. I have an aggregate function
> that takes a string and simply concatenates that string to the previous
> (internal state) value of the aggregate, example:
>
> "Hello:World" || ", " || "World:Hello" --> "Hello:World, World:Hello"
>
> My problem is that I sometimes get the same value before the colon
> sign and in those cases I should not add the whole string to the previous
> value of the aggregate but extract the value that is behind the colon and
> add it to already existing part which matched the value before the colon
> but with a slash as a delimiter, example:
>
> Internal state: "Hello:World, World:Hello"
> New value: "Hello:Dolly"
> After function is run: "Hello:World/Dolly, World:Hello"
>
> So what I am doing is a lot of strpos() and substr() functions (I have
> previously asked for the speed of the substr() function) but it is
> beginning to look really alwful.
>
> It seems very odd that there doesn't exist something else like what I need
> but I haven't found anything, although I admit I might not understand all
> aspects of the PostGreSQL database and what I can do with the SQL in
> connection to it.
>
> Below you will find my unfinished function, but it will show you what I
> mean when I say ugly..
>
> Any help is appreciated.
>
> Thanks in advance,
>
> Archie
>
>
> CREATE FUNCTION rarity_concat(text, text)
>    RETURNS text
>    AS
>      'DECLARE
>         colon_pos integer;
>         set_str text;
>         rarity_str text;
>         set_exist_pos integer;
>         rarity_exist_str_middle text;
>         rarity_exist_str_end text;
>       BEGIN
>         colon_pos := strpos($2, ':');
>         set_str := substr($2, 1, colon_pos);
>         set_exist_pos := strpos($1, set_str);
>         IF set_exist_pos > 0 THEN
>           rarity_str := substr($2, colon_pos + 2);
>           rarity_exist_str_start := substr($1, 1, set_exist_pos - 1);
>           comma_pos :=
>         ELSE
>            RETURN $1 || \', \' || $2;
>         END IF;
>       END'
>    LANGUAGE 'plpgsql';
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match

--
Chris Kratz