What is Varchar2 length limit?

[Ilya Portnov]

Hello.

I'm trying to understand, what is maximum length of varchar2 string? The readme states:

>> » Please note that unlike Oracle, orafce’s VARCHAR2 and NVARCHAR2 do not impose the 4000 bytes limit on the 'declared' size. In fact it is same as that of PostgreSQL varchar, which is about 10MB (although varchar can theoretically store values of size up to 1GB)

It's not quite clear to me what this means. PostgreSQL's plain varchar, if you do not specify (n), stores values up to 1GB. If you specify (n), then n must not be greater than 10*1024*1024, indeed.

I conveyed an experiment:

create table tstvarchar2(

  id int primary key,

  value varchar2(10485760)

);

insert into tstvarchar2 (id, value) values (1, generate_random_string(10485760));

And I got an error: row is too big: size ..., maximum size 8160.

I got the same error when I replaced varchar2(10*1024*1024) with just varchar2.

When I used plain PostgreSQL's varchar, I got no error either with varchar(10485760) nor with simple varchar.

I searched for the number 8160 in orafce's repo and found nothing. The only idea I have what it is - is that this is maximum tuple size in PostgreSQL (slightly less than one block). But shouldn't TOAST mechanism automatically fire when I try to insert larger values?

versions: PostgreSQL 14.2 on debian, orafce 3.22.0 from debian repos.

So, the questions are:

* where does this 8160 come from? Oracle's limit is either 4000 or 32k, depending on settings...

* is this a bug in orafce, in postgres, or is this intended behaviour? If so, where is it documented?

Best regards,

[Pavel Stehule]

so 30. 7. 2022 v 17:22 odesílatel Ilya Portnov <ilya.p...@gmail.com> napsal:

varchar2 and nvarchar2 are based on postgres's varchar type  - so there should be almost all limits. 1GB is a theoretical limit, but in reality due limits on allocations you can get some memory issues with shorter strings - safe value is about 500MB, and practical good value is about 10MB. Some other components in the stack (drivers, some interpreters) can have problems with long strings. The string is completely loaded to memory, so if you have too long a string, then there are big memory requests (and there is risk of using swap or OOM killer).

There is an error in the current orafce's implementation. It uses default typstorage 'p' plain which is probably wrong. It should to use 'x'. Probably there is new check (I don't know how it is old), that disallow using extended storage for this type

PLAIN storage is limited by size of data page (8KB) https://www.postgresql.org/docs/current/storage-toast.html

you can run (as user postgres) commands

alter type oracle.nvarchar2 set (storage = extended);

alter type oracle.varchar2 set (storage = extended);

and recreate tables

It will be fixed in orafce 3.24

Best regards,

--
You received this message because you are subscribed to the Google Groups "Better Oracle functions support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to orafce-genera...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/b3bd6eaa-5ad2-4f27-8603-356c2cf81ad6n%40googlegroups.com.

[Pavel Stehule]

so 30. 7. 2022 v 18:53 odesílatel Pavel Stehule <pavel....@gmail.com> napsal:

you can test it from https://github.com/orafce/orafce

https://github.com/orafce/orafce/commit/ed2dfa4dc0df8c3ae7663b9ca432e0a092219b74

Regards

Pavel

[Ilya Portnov]

Thanks for the prompt reply, and for the fix!

суббота, 30 июля 2022 г. в 22:21:24 UTC+5, Pavel Stehule:

[Pavel Stehule]

Thank you for bug report

Pavel

so 30. 7. 2022 v 19:52 odesílatel Ilya Portnov <ilya.p...@gmail.com> napsal:

To view this discussion on the web visit https://groups.google.com/d/msgid/orafce-general/acfeb1fa-ebac-48d8-9d4c-2545eb87300bn%40googlegroups.com.